SUM of grouped COUNT in SQL Query

SUM of grouped COUNT in SQL Query

SELECT name, COUNT(name) AS count
FROM table
GROUP BY name

UNION ALL

SELECT SUM name, COUNT(name)
FROM table

OUTPUT:

name                                               count
-------------------------------------------------- -----------
alpha                                              1
beta                                               3
Charlie                                            2
SUM                                                6
SELECT name, COUNT(name) AS count, SUM(COUNT(name)) OVER() AS total_count
FROM Table GROUP BY name

SUM of grouped COUNT in SQL Query

Without specifying which rdbms you are using

Have a look at this demo

SQL Fiddle DEMO

SELECT Name, COUNT(1) as Cnt
FROM Table1
GROUP BY Name
UNION ALL
SELECT SUM Name, COUNT(1)
FROM Table1

That said, I would recomend that the total be added by your presentation layer, and not by the database.

This is a bit more of a SQL SERVER Version using Summarizing Data Using ROLLUP

SQL Fiddle DEMO

SELECT CASE WHEN (GROUPING(NAME) = 1) THEN SUM
            ELSE ISNULL(NAME, UNKNOWN)
       END Name, 
      COUNT(1) as Cnt
FROM Table1
GROUP BY NAME
WITH ROLLUP

Leave a Reply

Your email address will not be published.