按组计数(*)

Question

我有一个查询，返回了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'。这种情况可能吗？

- Echilon

---

select count(1) from (select distinct ID, honame, ho_status from HeadOffice) ignore 返回什么结果？ - Sergey Kalinichenko

如果您的原始查询返回正确数量的行，为什么在包装Select count(*) from (<Your Original Query>)时要更改它？ - JeffO

Answer 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

- Eric

---

我之前是用COUNT(*)，但这个方法完美地解决了问题。谢谢。 - Echilon

Answer 2

您的列表将会新增一行，其中除了属性计数和属性值之外，其他所有内容都是空值。该记录将包含一个计数和所有属性值的总和。

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

- xQbert

Answer 3

这应该可以运行

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

- Bassam Mehanni