CODEWITHSUNDEEP
sqlsql-serversql-server-2008countgroup-by
Echilon
Echilon

Reputation: 10244

COUNT(*) of GROUP BY

I have a query which returns 644 rows, grouped by several columns. I need to get a count of these rows, 644.

This is the query:

SELECT DISTINCT ho.ID,ho.honame,ho.ho_status,SUM(Properties.Value) as [sales value], COUNT(Properties.record_id) as [property count]
FROM HeadOffice ho INNER JOIN Properties ON Properties.head_office_code = ho.id
WHERE Somecondition
GROUP BY ho.ID,ho.honame,ho_status ORDER BY ho_status

Despite trying COUNT(*), wrapping it in another query and removing the GROUP BY, I can't get '644' back. The closest I've come is 644 rows, all containing '1'. Is this possible?

Upvotes: 4

Views: 7066

Answers (3)

Eric
Eric

Reputation: 95133

The easy way to do it is thusly:

SELECT count(1) as NumRows from
(SELECT DISTINCT ho.ID,ho.honame,ho.ho_status,SUM(Properties.Value) as [sales value], COUNT(Properties.record_id) as [property count]
FROM HeadOffice ho INNER JOIN Properties ON clients.head_office_code = ho.id
WHERE Somecondition
GROUP BY ho.ID,ho.honame,ho_status ORDER BY ho_status) x

If you want the count plus your columns, use over:

SELECT 
    count(1) over () as NumRows,
    x.ID,
    x.ho_status,
    x.[sales value],
    x.[property count]
from
(SELECT DISTINCT ho.ID,ho.honame,ho.ho_status,SUM(Properties.Value) as [sales value], COUNT(Properties.record_id) as [property count]
FROM HeadOffice ho INNER JOIN Properties ON clients.head_office_code = ho.id
WHERE Somecondition
GROUP BY ho.ID,ho.honame,ho_status ORDER BY ho_status) x

Upvotes: 5

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

This should work

SELECT COUNT(ID) 
FROM (
SELECT DISTINCT ho.ID,ho.honame,ho.ho_status,
       SUM(Properties.Value) as [sales value], 
       COUNT(Properties.record_id) as [property count]
FROM HeadOffice ho 
     INNER JOIN Properties ON clients.head_office_code = ho.id
WHERE Somecondition
GROUP BY ho.ID, ho.honame, ho_status ) t

Upvotes: 0

xQbert
xQbert

Reputation: 35323

You will get 1 more row to your list which contains null values for everything but property count and properties value. That record will have a count and the sum of all property values.

SELECT DISTINCT ho.ID,ho.honame,ho.ho_status, 
SUM(Properties.Value) as [sales value], 
COUNT(Properties.record_id) as [property count]
FROM HeadOffice ho 
INNER JOIN Properties 
  ON clients.head_office_code = ho.id
WHERE Somecondition
GROUP BY Grouping sets((ho.ID,ho.honame,ho_status),()) 
ORDER BY ho_status

Upvotes: 2

Related Questions