sql - COUNT(*) 的 GROUP BY

我有一个查询，它返回 644 行，按几列分组。我需要对这些行进行计数，644。

这是查询:

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

尽管尝试了 COUNT(*) ，将它包装在另一个查询中并删除了 GROUP BY ，但我无法取回 '644' 。我最接近的是 644 行，所有行都包含“1”。这可能吗？

最佳答案

因此，最简单的方法是:

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

如果您想要计数加上您的列，请使用 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

关于sql - COUNT(*) 的 GROUP BY，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8791197/