I tried to SUM values of multiple columns (CASH CARD CHEQUE REST) and group them to display totals for each day.
Actual table:
| Date | CASH | CARD | CHEQUE | REST | TYPE |
|---|---|---|---|---|---|
| 2022-06-02 | 150.00 | 200.00 | 0.00 | 12.00 | STORE1 |
| 2022-06-02 | 150.00 | 240.00 | 56.00 | 67.00 | STORE2 |
| 2022-06-02 | 45.00 | 459.00 | 150.00 | 0.00 | STORE3 |
| 2022-06-02 | 45.00 | 400.00 | 150.00 | 34.00 | TRAVEL1 |
| 2022-06-03 | 87.00 | 59.00 | 150.00 | 400.00 | STORE1 |
| 2022-06-03 | 45.00 | 790.00 | 450.00 | 104.00 | STORE2 |
| 2022-06-03 | 70.00 | 30.00 | 0.00 | 241.00 | STORE3 |
| 2022-06-03 | 30.00 | 120.00 | 11.00 | 72.00 | TRAVEL1 |
I want it to make it as new table as:
| Date | TOTAL_SALE | STORE_TOTAL | TRAVEL_TOTAL |
|---|---|---|---|
| 2022-06-02 | 2158.00 | 1529.00 | 629.00 |
| 2022-06-03 | 2659.00 | 2426.00 | 233.00 |
I tried with UNION but that puts the result one below other and not like this in the same row, I also tried the option below but am getting this error
#1111 invalid use of group function
Code I use that produces an error:
SELECT DATE, (SUM(CASH) SUM(CARD) SUM(CHEQUE) SUM(REST)) AS TOTAL_SALE,
SUM(case when TYPE LIKE 'STORE%' then (SUM(CASH) SUM(CARD) SUM(CHEQUE) SUM(REST))
else 0 end) as STORE_TOTAL,
SUM(case when TYPE LIKE 'TRAVEL%' then (SUM(CASH) SUM(CARD) SUM(CHEQUE) SUM(REST))
else 0 end) as TRAVEL_TOTAL
FROM tbl_Payment where DATE BETWEEN '2022-06-02' AND '2022-06-03'
GROUP BY DATE ASC
CodePudding user response：
First, group by does not take an order. No asc.
The problem is putting sum inside a sum. Whatever is inside sum will be summed per row, there's no need to sum it again.
Similarly, you don't have to sum each column and then add the sums. Add the columns then sum. This is a bit more succinct.
SELECT
"DATE",
SUM(CASH CARD CHEQUE REST) AS TOTAL_SALE,
SUM(
case when TYPE LIKE 'STORE%' then
CASH CARD CHEQUE REST
else
0
end
) as STORE_TOTAL,
SUM(
case when TYPE LIKE 'TRAVEL%' then
CASH CARD CHEQUE REST
else
0
end
) as TRAVEL_TOTAL
FROM tbl_Payment
where "DATE" BETWEEN '2022-06-02' AND '2022-06-03'
GROUP BY "DATE"
Note: date is a SQL keyword. It can be confused with the type date. Avoid using it as a column name. Use the at and on conventions for naming timestamp and date columns. For example, paid_on.