Skip to Content
0
Former Member
May 04, 2012 at 02:41 PM

Query with GROUP BY function

15 Views

Hi all,

I have the following query:

SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[LineTotal] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[CardCode] >=[%0] and T0.[CardCode] <=[%1] and T0.[DocDate] >=[%2] and T0.[DocDate] <=[%3]

GROUP BY T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T1.LineTotal

Currently it is returning something along the lines:

# Customer/Supplier Code Customer/Supplier Name Item No. Item/Service Description Quantity Row Total 1 C20000 Norm Thompson A00001 IBM Infoprint 1312 1 300 2 C20000 Norm Thompson A00001 IBM Infoprint 1312 1 1,500.00 3 C20000 Norm Thompson A00001 IBM Infoprint 1312 5 1,500.00 4 C20000 Norm Thompson A00001 IBM Infoprint 1312 10 3,000.00 5 C20000 Norm Thompson A00001 IBM Infoprint 1312 20 6,000.00 6 C20000 Norm Thompson A00001 IBM Infoprint 1312 25 7,500.00 7 C20000 Norm Thompson A00001 IBM Infoprint 1312 30 9,000.00

Where as I want it to return something along the lines of:

# Customer/Supplier Code Customer/Supplier Name Item No. Item/Service Description Quantity Row Total 1 C20000 Norm Thompson A00001 IBM Infoprint 1312 92 28800

I want it to be grouped by Item code, quantity and line total. Can someone please help on this one please?

Have tried to group this together and take out card code and/or card name but will not allow and wont return what I am looking for.

Thanks very much,

Brian