通过一次查询使用多个组 [英] Using multiple group by having in single query

问题描述

我有2个查询来获取具有count = 1和count = 2的家庭的数量.

I have 2 queries to get the count of families having count = 1 and count = 2.

SELECT Name, count(*) as c FROM Tablename GROUP BY HOUSE_NO HAVING c<=1;


 SELECT Name, count(*) as c FROM Tablename GROUP BY  HOUSE_NO HAVING c>=2 and c<=4;

但是我需要将这些查询合并为一个查询.

But i need to combine those queries into single query.Like

         count1                          count2
  nooffamiliesHavingcount = 1        nooffamiliesHavingcount = 2

请帮助我....谢谢.

Please help me....Thanks in advance..

推荐答案

您需要将第一个计数放入子查询中:

You need to put your first count into a subquery:

SELECT  COUNT(CASE WHEN C = 1 THEN 1 END) AS nooffamiliesHavingcount1,
        COUNT(CASE WHEN C = 2 THEN 1 END) AS nooffamiliesHavingcount2
FROM    (   SELECT  COUNT(*) AS C
            FROM    TableName
            GROUP BY House_No
        ) t
WHERE   c IN (1, 2);

编辑

如果您需要计算计数范围，可以使用以下方法:

If you need to do ranges in your count you can use this:

SELECT  COUNT(CASE WHEN C <= 1 THEN 1 END) AS nooffamiliesHavingcount1,
        COUNT(CASE WHEN C BETWEEN 2 AND 4 THEN 1 END) AS nooffamiliesHavingcount2,
        COUNT(CASE WHEN C > 4 THEN 1 END) AS nooffamiliesHavingcount3
FROM    (   SELECT  COUNT(*) AS C
            FROM    TableName
            GROUP BY House_No
        ) t

关于SQL提琴的示例

Example on SQL Fiddle

这篇关于通过一次查询使用多个组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！