CODEWITHSUNDEEP
mysqlsql
J. Doe
J. Doe

Reputation: 1

SQL: count number of occurrences between two values

I'm embarrassed to ask such a trivial SQL question, but I can't seem to find nor develop the answer.

I have a column of IDs, some of which occur more than once. I would like to count the number of occurrences of each ID and group them accordingly.

For example:

IDs: 112 113 114 115 112 112 112

Arbitrary groupings: 1, 2-5, >5

Ending up with a return

NUMBER OCCURRENCES 
1      3 
2-5    1
6+     0

Code?

SELECT "1" as NUMBER, 
COUNT(ID) AS OCCURRENCES 
FROM TABLE WHERE OCCURRENCES = 1
UNION
SELECT "2-5" as NUMBER,
COUNT(ID) AS OCCURRENCES
FROM TABLE WHERE OCCURRENCES BETWEEN 2 AND 5
UNION
SELECT "6+" as NUMBER,
COUNT(ID) AS OCCURRENCES
FROM TABLE WHERE OCCURRENCES > 5

Thank you for any help,

Upvotes: 0

Views: 1527

Answers (2)

addohm
addohm

Reputation: 2475

Just based on your description of the task this should get you going.

SELECT [ID], COUNT([ID]) AS OCCURRENCES FROM t GROUP BY [ID], ORDER BY OCCURRENCES DESC

This will produce a list of all the ID's and the count of the occurrences associated to the ID in descending order. I went this route since you mentioned that the ranges arbitrary.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270411

I think you want basically a histogram-of-histograms:

select cnt, count(*), min(id), max(id)
from (select id, count(*) as cnt
      from t
      group by id
     ) t
group by cnt;

You can add ranges to this:

select (case when cnt = 1 then '1'
             when cnt <= 5 then '2-5'
             else '6+'
        end) as cnt_range, count(*), min(id), max(id)
from (select id, count(*) as cnt
      from t
      group by id
     ) t
group by cnt_range
order by min(cnt);

This will not produce the 0 value. I'm not sure if that is really important for what you need to do.

Upvotes: 2

Related Questions