sumar - sql contar registros agrupados

SUMA de COUNT agrupados en consulta SQL

Question

Tengo una mesa con 2 campos:

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

Quiero agruparlos por nombre, con ''contar'' y una fila ''SUM''

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

¿Cómo escribiría una consulta para agregar una fila SUM debajo de la tabla?

Answer 1

Después de la consulta, ejecute a continuación para obtener el recuento total de filas

select @@ROWCOUNT

Answer 2

Para el servidor SQL puede probar este.

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

Answer 3

Por favor, ejecute de la siguiente manera:

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

Answer 4

Prueba esto:

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

Answer 5

Puede intentar agrupar por nombre y contar los identificadores en ese grupo.

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

Answer 6

Puedes usar ROLLUP

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

Answer 7

Puedes usar union para unir filas.

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

Answer 8

Sin especificar qué rdbms está utilizando

Echa un vistazo a esta demo.

DEMO de SQL Fiddle

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

Dicho esto, recomendaría que la capa de presentación agregue el total, y no la base de datos.

Esto es un poco más de una versión del SERVIDOR SQL utilizando Resumir datos utilizando ROLLUP

DEMO de SQL Fiddle

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

Answer 9

Usalo como

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

Answer 10

seleccione la (s) suma (s) de (seleccione recuento (Nombre_Col) como s del grupo Tab_name; Col_name contando (*)> 1) c

Answer 11

SELECT name, COUNT(name) AS count
FROM table
GROUP BY name
UNION ALL
SELECT ''SUM'' name, COUNT(name)
FROM table

SALIDA:

name count
-------------------------------------------------- -----------
alpha 1
beta 3
Charlie 2
SUM 6

Answer 12

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

Answer 13

 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