SQL 查询中分组计数的求和

小开

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

小开

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

小开

最佳答案

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

小开

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

小开

Use it as

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

小开

Try this:

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

小开

For Sql server you can try this one.

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

小开

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

小开

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

select @@ROWCOUNT

小开

Please run as below :

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

小开

  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

小开

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

小开

You can use ROLLUP

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

小开

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

小开

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`;

小开

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 ;

小开

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