subtotals

Hi Jacquie,

If you want to return subtotals when executing a select statement then you need to use either the WITH CUBE or WITH ROLLUP clauses. The following example will give subtotals for sales invoices grouped by customer code and item group code:

select case

when (grouping(a.CardCode) = 1) then'ALL'

else isnull(a.CardCode, 'UNKNOWN')

end as CardCode,

c.ItmsGrpCod,

sum(b.LineTotal) as SalesTotal

from OINV a inner join INV1 b on a.DocEntry = b.DocEntry inner join OITM c on b.ItemCode = c.ItemCode

group by a.CardCode, c.ItmsGrpCod with CUBE

Hope this helps,

Owen