SUM multiple columns by different condition from same table and then group by date

Question

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

Answer 1

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.