CODEWITHSUNDEEP
mysqlsqlselectcountsum
BlueFox
BlueFox

Reputation: 171

SQL: Count column and show the total result

This is my current select on my table:

SELECT count(*) as Titel, KuenstlerName FROM Bild GROUP BY KuenstlerName;
+-------+---------------+
| Titel | KuenstlerName |
+-------+---------------+
|     3 | Klee          |
|     1 | Klingt        |
|     1 | Marc          |
|     6 | Picasso       |
|     1 | van Gogh      |
+-------+---------------+

Is there a way to add a new row/column or just display a message which returns the total values of the column 'Titel'.

For Example:

+-------+---------------+
| Titel | KuenstlerName |
+-------+---------------+
|     3 | Klee          |
|     1 | Klingt        |
|     1 | Marc          |
|     6 | Picasso       |
|     1 | van Gogh      |
|    12 | Total         |
+-------+---------------+

+-------+---------------+-----+
| Titel | KuenstlerName |Total|
+-------+---------------+-----+
|     3 | Klee          |  12 |
|     1 | Klingt        |     |
|     1 | Marc          |     |
|     6 | Picasso       |     |
|     1 | van Gogh      |     |
+-------+---------------+-----+

or:

+-------+---------------+
| Titel | KuenstlerName |
+-------+---------------+
|     3 | Klee          |
|     1 | Klingt        |
|     1 | Marc          |
|     6 | Picasso       |
|     1 | van Gogh      |
+-------+---------------+
Total: 12

Thanks for your help!

Upvotes: 1

Views: 135

Answers (4)

noobdeveloper
noobdeveloper

Reputation: 420

Try this:

SELECT sum(titel),coalesce(KuenstlerName,'Total') FROM testdb.titel GROUP BY KuenstlerName with rollup;

Thanks.

Added screenshot of result.

enter image description here

Upvotes: 0

Samuil Banti
Samuil Banti

Reputation: 1795

SELECT KuenstlerName, count(*) as Titel FROM Bild GROUP BY KuenstlerName
UNION 
SELECT "Total:" AS KuenstlerName, count(*) AS Titel FROM Bild

Upvotes: 0

BarneyL
BarneyL

Reputation: 1362

Probably not the most elegant solution but this would work:

SELECT count(*) as Titel, KuenstlerName 
FROM Bild 
GROUP BY KuenstlerName
UNION ALL
SELECT count(*) as Titel, 'Total' AS KuenstlerName 
FROM Bild

(someone jumped in with virtually the same solution while I was typing, UNION ALL should be marginally faster than UNION alone though).

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT count(*) as Titel, KuenstlerName FROM Bild GROUP BY KuenstlerName
UNION 
SELECT count(*),'Total' FROM Bild

Upvotes: 0

Related Questions