I have a table of restaurants:
| name | year | profit |
|---|---|---|
| restaurant1 | 2016 | 112000 |
| restaurant1 | 2018 | 150000 |
| restaurant1 | 2020 | 165000 |
| restaurant2 | 2018 | 40000 |
| restaurant2 | 2019 | 52000 |
| restaurant3 | 2017 | 64000 |
| restaurant3 | 2018 | 73000 |
| restaurant3 | 2019 | 76000 |
I want to sum the profit of each restaurant grouped by the restaurant name, and display the total profit after each restaurant, like so:
| name | year | profit |
|---|---|---|
| restaurant1 | 2016 | 112000 |
| restaurant1 | 2018 | 150000 |
| restaurant1 | 2020 | 165000 |
| restaurant1 | total | 427000 |
| restaurant2 | 2018 | 40000 |
| restaurant2 | 2019 | 52000 |
| restaurant 2 | total | 92000 |
| restaurant3 | 2017 | 64000 |
| restaurant3 | 2018 | 73000 |
| restaurant3 | 2019 | 76000 |
| restaurant3 | total | 231000 |
I know that I can get the total profit of each restaurant with SELECT SUM(profit) FROM restaurants GROUP BY restaurants.name;
My question is how to display this result the way I have shown on the second table.
CodePudding user response：
just use UNION :
SELECT name, year :: text, profit FROM restaurants
UNION
(SELECT name, 'total', SUM(profit) FROM restaurants GROUP BY restaurants.name)
ORDER BY name, year
see the result in dbfiddle