[sql] SUM of grouped COUNT in SQL Query

I have a table with 2 fields:

ID  Name
--  -------
1   Alpha
2   Beta
3   Beta
4   Beta
5   Charlie
6   Charlie

I want to group them by name, with 'count', and a row 'SUM'

Name     Count
-------  -----
Alpha     1
Beta      3
Charlie   2
SUM       6

How would I write a query to add SUM row below the table?

The way I interpreted this question is needing the subtotal value of each group of answers. Subtotaling turns out to be very easy, using PARTITION:

SUM(COUNT(0)) OVER (PARTITION BY [Grouping]) AS [MY_TOTAL]

This is what my full SQL call looks like:

SELECT MAX(GroupName) [name], MAX(AUX2)[type],  
COUNT(0) [count], SUM(COUNT(0)) OVER(PARTITION BY GroupId) AS [total]
    FROM [MyView]
  WHERE Active=1 AND Type='APP' AND Completed=1
    AND [Date] BETWEEN '01/01/2014' AND GETDATE()
    AND Id = '5b9xxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' AND GroupId IS NOT NULL
  GROUP BY AUX2, GroupId

The data returned from this looks like:

name    type    count   total
Training Group 2    Cancelation 1   52
Training Group 2    Completed   41  52
Training Group 2    No Show 6   52
Training Group 2    Rescheduled 4   52
Training Group 3    NULL        4   10535
Training Group 3    Cancelation 857 10535
Training Group 3    Completed   7923    10535
Training Group 3    No Show 292 10535
Training Group 3    Rescheduled 1459    10535
Training Group 4    Cancelation 2   27
Training Group 4    Completed   24  27
Training Group 4    Rescheduled 1   27

Please run as below :

Select sum(count) 
  from (select Name, 
               count(Name) as Count 
          from YourTable
      group by Name);  -- 6

I am using SQL server and the following should work for you:

select cast(name as varchar(16)) as 'Name', count(name) as 'Count' from Table1 group by Name union all select 'Sum:', count(name) from Table1

For Sql server you can try this one.

SELECT ISNULL([NAME],'SUM'),Count([NAME]) AS COUNT
FROM TABLENAME
GROUP BY [NAME] WITH CUBE

select sum(s) from (select count(Col_name) as s from Tab_name group by Col_name having count(*)>1)c

SELECT name, COUNT(name) AS count, SUM(COUNT(name)) OVER() AS total_count
FROM Table GROUP BY name

Try this:

SELECT  ISNULL(Name,'SUM'), count(*) as Count
FROM table_name
Group By Name
WITH ROLLUP

You can use union to joining rows.

select Name, count(*) as Count from yourTable group by Name
union all
select "SUM" as Name, count(*) as Count from yourTable

You can try group by on name and count the ids in that group.

SELECT name, count(id) as COUNT FROM table group by name

You can use ROLLUP

select nvl(name, 'SUM'), count(*)
from table
group by rollup(name)

Use it as

select Name, count(Name) as Count from YourTable
group by Name
union 
Select 'SUM' , COUNT(Name) from YourTable

After the query, run below to get the total row count

select @@ROWCOUNT

I required having count(*) > 1 also. So, I wrote my own query after referring some the above queries

SYNTAX:

select sum(count) from (select count(`table_name`.`id`) as `count` from `table_name` where {some condition} group by {some_column} having count(`table_name`.`id`) > 1) as `tmp`;

Example:

select sum(count) from (select count(`table_name`.`id`) as `count` from `table_name` where `table_name`.`name` IS NOT NULL and `table_name`.`name` != '' group by `table_name`.`name` having count(`table_name`.`id`) > 1) as `tmp`;

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

  with cttmp
  as
  (
  select Col_Name, count(*) as ctn from tab_name group by Col_Name having count(Col_Name)>1
  )
  select sum(ctn) from c

all of the solution here are great but not necessarily can be implemented for old mysql servers (at least at my case). so you can use sub-queries (i think it is less complicated).

 select sum(t1.cnt) from 
        (SELECT column, COUNT(column) as cnt
            FROM
            table 
            GROUP BY 
            column
            HAVING 
            COUNT(column) > 1) as t1 ;