1

I have the following table (only one column):

X
----
1
1
1
2
2
3
3
3
4
4
5
6
6
7
7
7

I need a SQL query which will output the following:

Occurrences | Number
1  1  * there are only 1 entry with one occurrence (number 5)
2  3  * there are three entries with two occurrences (numbers 2,4,6)
3  2  * there are two entries with three occurrences (numbers 1 and 7)

If I do something like this:

select count(*) FROM (SELECT count(*) as N FROM table 1 GROUP BY x) a GROUP BY a.N;

I get the correct results, but only the second column t with the number of occurrences. Does anyone know how to get the complete output as in the example above?

| improve this question | | | | |
  • 1
    There are 3 entries with three occurances. – krithikaGopalakrisnan Sep 24 '18 at 7:29
  • 1
    Which DBMS is this for? (Postgres, SQL Server, DB2, SQLite, MySQL, Oracle, ...) There are tens and the solution may differ. – ypercubeᵀᴹ Sep 24 '18 at 10:17
  • This sounds suspiciously like you're trying to dumb down the real question. What possible use is a table with a single column? If this is not the real question you need answered, please edit your question to show the actual real-world question. Add CREATE TABLE statements, with INSERT INTO ... statements for the data, along with expected output in table form. Check these details for an example. – Max Vernon Sep 24 '18 at 16:24
1

This should work:

select count(*), a.N FROM (SELECT count(*) as N FROM table1 GROUP BY x) a GROUP BY a.N;

SQLFiddle

Note that it gives you 3 for N = 3, because there are actually three number repeated thrice: 1, 3 and 7.

enter image description here

| improve this answer | | | | |
0

This is the query you might need:

select t.count as Occurrences, count(*) as Number from
(select a, count(*) as count from table_1 group by a)t 
group by t.count 
order by Occurrences;
| improve this answer | | | | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.