Skip to Content
author's profile photo Former Member
Former Member

Query with GROUP BY function

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on May 04, 2012 at 03:06 PM

    SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], SUM(T1.[Quantity]), SUM(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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 04, 2012 at 03:13 PM

    Hi,

    Check this :

    SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], sum(T1.[Quantity]),

    sum(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

    Kind Regards,

    Jitin

    SAP Business One Forum Team

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.