AnsweredAssumed Answered

SQL: How to GROUP BY one column when multiple are selected?

Question asked by openspace on Mar 1, 2015
Latest reply on Mar 3, 2015 by openspace

I have a table "Line Items" that contains a line from each invoice. I'm trying to pull information from this table using sql in order to create some nice stats for the dashboard.

 

My question is, can I select a list of items from the Line Items table like these:

Book, 1, 20

Towel, 1, 10

Towel 1, 10

Book, 2, 20

Chair, 1, 30

 

and turn them into a list like this using sql?:

Book, 3, 20

Towel, 2, 10

Chair, 1, 30

 

 

Essentially I would like to use the following code, but I the only way I can get it to work is to change the GROUP BY STATEMENT to GROUP BY Item, Qty, Amount. Which results with an incorrect and reordered version of the first list above.

 

ExecuteSQL( "
SELECT Item, Qty, Amount
FROM \"Line Items\"
WHERE DatePayment <= EndDate AND DatePayment >=StartDate
AND Category = 'Product'
GROUP BY Item
ORDER BY Qty DESC
"; ""; ¶; "")

Outcomes