在groupby中使用groupby [英] Using groupby in groupby

问题描述

我有一张桌子，如下表

user_id HouseNo      Name   location_id
 1     0-0        aaa    1
 2     0-0        bbb    1
 3     0-1        ccc    1
 4     1-2        ddd    1
 5     2-1        eee    1
 6     1-2        fff    1
 7     2-1        ggg    1
 8     1-2        hhh    1
 9     2-1        iii    1
 10    3-0        jjj    1
 11    3-0        kkk    1
 12    3-0        lll    1

我在这里有2个问题

1)我需要按房屋编号获得用户数，为此 从用户组中按house_no选择house_no，count(user_id);

1)I need to get no of users by house no.For that i used select house_no,count(user_id) from user group by house_no;

然后我得到了结果

    house_no count(house_no)
    0-0        2
    0-1        1
    1-2        3
    2-1        3
    3-0        3

对我很好.

但是我的问题是我怎样才能得到家庭数为1的家庭和家庭数为2 ......

But my question is how i can get the no of families which has count 1 and the no of families which has count 2 ......

Like as follows:


 count1   1
 count2   1
 count3   3

这里count1表示没有一个用户的家庭，count2表示没有两个用户的家庭...

Here count1 means no of families having one user,count2 means no of families having two users......

我这里有3个固定条件，它们是count <= 3 count> 4和 计数< 10计数> 10

I have 3 fixed conditions here.They are count<=3 count >4 and count<10 count >10

我需要如下结果

count1   count2 count3
1         1       3

注意:这里相同的家庭表示包含相同房子编号的用户. 请帮助我...谢谢....

NOTE:Here same family means users containing same houseno. Please help me...Thanks in advance....

推荐答案

下面的查询分为两部分，第一部分是子查询，该子查询计算每个House_No的成员数.然后再次计算子查询的结果，这一次它将作为成员数进行分组.

The query below has two parts, the first part is the subquery which calculates the Number of members per House_No. The result of the subquery is then calculated again and this time it will be group as the number of member.

SELECT  CONCAT('COUNT', totalMember) FamilyMember, COUNT(*) NumberOfFamilies
FROM
        (
            SELECT  House_No, COUNT(*) totalMember
            FROM    TableName
            GROUP   BY House_no
        ) x
GROUP   BY totalMember
ORDER   BY totalMember

• SQLFiddle演示
• SQLFiddle Demo

输出

╔══════════════╦══════════════════╗
║ FAMILYMEMBER ║ NUMBEROFFAMILIES ║
╠══════════════╬══════════════════╣
║ COUNT1       ║                1 ║
║ COUNT2       ║                1 ║
║ COUNT3       ║                3 ║
╚══════════════╩══════════════════╝

这篇关于在groupby中使用groupby的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！