Geek asks and answers

SQL to group based on column columns

The 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

Thus, it basically tracks the progress of the state for different users.

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

The expected result will be something like this:

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

I tried, but cannot find a solution. Any syntax is fine. SQL / MySQL / DB2 / Oracle.

+3
source share
4 answers

If you do not like to use a subquery, you can avoid it like this:

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

You will need a subquery:

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

sqlfiddle: http://sqlfiddle.com/#!2/ed95e/1

+8
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
;
+5


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

,

+1

More articles:


All Articles