Consider this table
| name | amount |
|---|---|
| cat | 1 |
| cat | 5 |
| horse | 2 |
Now I want to find sum(amount) group by name like this:
SELECT name,SUM(amount) s FROM animals GROUP BY name;
Result will be like:
| name | s |
|---|---|
| cat | 6 |
| horse | 2 |
I want to find sum(s)
My question is that, is anyone to know how can I find sum(s) with a query
SELECT SUM(s),(SELECT name, SUM(amount) s FROM animals GROUP BY name);
But This query not working.
Please help me
CodePudding user response：
If you need in total sum only then simply remove GROUP BY:
SELECT name, SUM(amount) summ
FROM animals;
If you need sums for each animal and total then use WITH ROLLUP:
SELECT COALESCE(name, 'Total') name, SUM(amount) summ
FROM animals
GROUP BY name WITH ROLLUP;