daily average by rider and day-CodePudding

I have a table

id | price | date
1  | 4.45  | 2016-05-16
1  | 8.46  | 2016-05-16
2  | 11.9  | 2016-05-16
3  | 6.76  | 2016-05-16
3  | 13.55 | 2016-05-16
4  | 4.91  | 2016-05-20
1  | 16.77 | 2016-05-20
3  | 16.18 | 2016-05-20
2  | 6.07  | 2016-05-20
4  | 6.25  | 2016-05-20

I want to calculate average price per id and per day and the overall

date       | id   | avg_price
2016-05-16 |  1   |   6.45
2016-05-16 |  2   |   12.72
2016-05-16 |  3   |   6.76
2016-05-16 |Total |   9.02
2016-05-20 |  1   |   16.77
2016-05-20 |  2   |   6.07
2016-05-20 |  3   |   16.18
2016-05-20 |  4   |   5.58
2016-05-20 |Total |   10.04
Overall    |Total |   9.53

I have intermediate SQL skills. I know how to do the average of riders but the total per day and overall is something I cannot figure out how to do

CodePudding user response：

You need to group by date and id and use WITH ROLLUP modifier to get the totals:

SELECT COALESCE(date, 'Overall') date, 
       COALESCE(id, 'Total') id, 
       ROUND(AVG(price), 2) avg_price
FROM tablename
GROUP BY date, id
WITH ROLLUP;

Or, for MySql 8.0 :

SELECT CASE WHEN GROUPING(date) THEN 'Overall' ELSE date END date, 
       CASE WHEN GROUPING(id) THEN 'Total' ELSE id END id, 
       ROUND(AVG(price), 2) avg_price
FROM tablename
GROUP BY date, id
WITH ROLLUP;

See the demo.