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?

closed as unclear what you're asking by ypercubeᵀᴹ, Md Haidar Ali Khan, Marco, Max Vernon, MDCCL Sep 24 '18 at 16:30

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the 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

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;

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