SubTotals

Question

User-797751191 posted

Hi

  I want to display Subtotal by P.O # & ItemCode

P.O #	Vendor	Vendor Ref#	Item Code	Item Description	P.O Qty	P.O Price	Recd Qty
1	XYZ	NULL	I01	Ball Bearing - 6201	40	1100000	NULL
2	XYZ	12	I02	Ball Bearing - 6202	50	1375000	19.97
2	XYZ	12	I02	Ball Bearing - 6203	50	1375000	19.87
2	XYZ	12	I02	Ball Bearing - 6204	50	1375000	19.84

Thanks

Answer 1

User452040443 posted

Hi,

Try something like this:

select
    [PO],
    [Vendor],
    [VendorRef],
    [ItemCode],
    [ItemDescription],
    sum([POQty]) as POQty,
    [POPrice],
    [RecdQty]
from MyTable
group by grouping sets
    ( ( [PO], [Vendor], [VendorRef], [ItemCode], [ItemDescription], [POQty], [POPrice], [RecdQty] ),
      ( [PO], [ItemCode] ) )

Hope this help

Answer 2

User948188685 posted

You can also try:

select distinct
    [PO],
    [Vendor],
    [VendorRef],
    [ItemCode],
    [ItemDescription],
    sum([POQty]) over(partition by [PO], [ItemCode]) as POQty,
    [POPrice],
    [RecdQty]
from MyTable