query command

[nitiphone2021]

I have a table include column name
license_plate(String),car_bike(1 or 2),price,create_date

I would like to create a report about income for system
I try as below command
SELECT DATE(create_date) as Park_Date,car_bike,price,COUNT(car_bike) * price as income from 1_tb_parking group by Park_Date,car_bike

I would like to get result like this

date, car_bike = 1, count(car_bike=1),sum(price for car_bike = 1) as sum1, car_bike = 2, count(car_bike=2),sum(price for car_bike = 2) as sum2, Total = sum1 + sum2

Do you have any idea?

[Barand]

input

+----+---------------+----------+-------+---------------------+
| id | license_plate | car_bike | price | create_date         |
+----+---------------+----------+-------+---------------------+
| 1  | BA12ECD       | 1        | 5.50  | 2021-08-21 08:09:51 |
| 3  | DA12ECD       | 1        | 8.50  | 2021-08-21 08:09:51 |
| 6  | GA12ECD       | 1        | 5.50  | 2021-08-21 08:09:51 |
| 7  | HA12ECD       | 1        | 8.50  | 2021-08-21 08:09:51 |
| 2  |               | 2        | 1.50  | 2021-08-21 08:09:51 |
| 4  |               | 2        | 1.50  | 2021-08-21 08:09:51 |
| 5  |               | 2        | 1.50  | 2021-08-21 08:09:51 |
| 8  | BB12ECD       | 1        | 5.50  | 2021-08-22 08:09:51 |
| 10 | DB12ECD       | 1        | 5.50  | 2021-08-22 08:09:51 |
| 13 | GB12ECD       | 1        | 9.50  | 2021-08-22 08:09:51 |
| 14 | HB12ECD       | 1        | 5.50  | 2021-08-22 08:09:51 |
| 9  |               | 2        | 1.50  | 2021-08-22 08:09:51 |
+----+---------------+----------+-------+---------------------+

query

SELECT date(create_date) as parking_date
     , car_bike
     , count(*) as num
     , sum(price) as tot
FROM tb_parking_group
GROUP BY parking_date, car_bike
WITH ROLLUP;

output

+--------------+----------+-----+-------+
| parking_date | car_bike | num | tot   |
+--------------+----------+-----+-------+
| 2021-08-21   |        1 |   4 | 28.00 |
| 2021-08-21   |        2 |   3 |  4.50 |
| 2021-08-21   |     NULL |   7 | 32.50 |    <-- day subtotal
| 2021-08-22   |        1 |   4 | 26.00 |
| 2021-08-22   |        2 |   1 |  1.50 |
| 2021-08-22   |     NULL |   5 | 27.50 |    <-- day subtotal
| NULL         |     NULL |  12 | 60.00 |    <-- total
+--------------+----------+-----+-------+