10 Replies Latest reply on Mar 3, 2015 1:21 AM by openspace

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


      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
      "; ""; ¶; "")