Is there a function in SQL that can display subtotal rows. I have a table like this:
Date INVNUNBER CUSTOMER ITEM QTY SALES
20190630 IN3343 joe's comp 23225 2.0 3000
20190630 IN3343 joe's comp 23214 1.0 400
20190630 IN3353 matt's comp. 12222 3.0 6000
20190630 IN3353 matt's comp. 32222 3.0 3000
I tried ROLLUP, but seems like ROLLUP requires an aggregate function where I have to SUM up one of the fields, and all other fields need to be in the Group By
clause, but I don't really need anything grouped:
I tried:
SELECT DATE, INVNUMBER, CUSTOMER, ITEM, QUANTITY, SALES
FROM OESHDT
WHERE DATE = '20190630'
GROUP BY DATE, INVNUMBER, CUSTOMER WITH ROLLUP
then I get:
Column 'OESHDT.ITEM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I just want to get the subtotals for each invoice number like this:
Date INVNUNBER CUSTOMER ITEM QTY SALES
20190630 IN3343 joe's comp 23225 2.0 3000
20190630 IN3343 joe's comp 23214 1.0 400
3.0 3400
20190630 IN3353 matt's comp. 12222 3.0 6000
20190630 IN3353 matt's comp. 32222 3.0 3000
6.0 9000
Since I'm not summing up anything and I only want subtotals for each, can SQL do this?
See Question&Answers more detail:os