CODEWITHSUNDEEP
mysqlsqloracle-databasedb2
Isaac
Isaac

Reputation: 2721

SQL to group based on the counts of a column

A hypothetical table contains the following coloumns:

id integer,
name char(6),
status integer

And has the following data:

id    id2     type
---   -----   ----
01    Adam    1
02    Bob     1
03    Adam    2
04    Caymen  1
05    Ahmed   1
06    Basel   1
07    Ahmed   2
08    Bob     2
09    Ahmed   2
10    Mike    1

So it basically tracks status progression for different users.

I want to group on the count of statuses. In other words, I want to know how many users have only 1 status, how many have 2 statuses, how many have 3 statuses, etc.

The expected output would be something like this:

num_of_statuses    count
---------------    -----
1                  3
2                  2
3                  1

I tried with having, but cannot find a solution yet. Any syntax is OK. SQL/MySQL/DB2/Oracle.

Upvotes: 3

Views: 244

Answers (4)

Andriy M
Andriy M

Reputation: 77677

If you don't feel like using a nested query, you could avoid it like this:

SELECT DISTINCT
  num_of_statuses = COUNT(*),
  count           = COUNT(*) OVER (PARTITION BY COUNT(*))
FROM atable
GROUP BY id

Upvotes: 2

Rohan
Rohan

Reputation: 2030

You may try this


SELECT DISTINCT NO_OF_STATUS, COUNT(NAME)
  FROM (SELECT DISTINCT HT.NAME, COUNT(HT.STATUS) AS NO_OF_STATUS
          FROM SO_HYPO_TABLE HT
         GROUP BY HT.NAME) A
 GROUP BY NO_OF_STATUS
 ORDER BY NO_OF_STATUS

Hope this helps

Upvotes: 1

Fred Sobotka
Fred Sobotka

Reputation: 5332

WITH summary (fname, num_of_statuses) AS
(    SELECT fname, COUNT(*) num_of_statuses 
     FROM basetablename GROUP BY fname
)
SELECT num_of_statuses, COUNT(*) AS peoplecount
FROM summary
GROUP BY num_of_statuses
;

Upvotes: 5

Chad
Chad

Reputation: 7507

You'll need a nested query like so:

select num_of_statuses, count(*)
from (
    select id2, count(*) as num_of_statuses
    from table1
    group by id2
) A
group by num_of_statuses

See it on sqlfiddle: http://sqlfiddle.com/#!2/ed95e/1

Upvotes: 8

Related Questions