samedi 13 juin 2015

SQLite: Count the number of similar COUNT(*) rows resulting from a GROUP_BY statement

I'm not very good at SQL, and I have a very peculiar request to do.

My table looks something like this :

FOO  BAR  
----+----
foo1 bar1
foo2 bar3
foo1 bar1
foo1 bar1
foo2 bar3 
foo4 bar3 
foo3 bar2 
foo2 bar4 
foo5 bar4

I manage easily to count the number of each different "bar" entries with a

SELECT bar, COUNT(*) as barcount FROM table GROUP BY bar ORDER BY barcount

which gives me

BAR  barcount
----+----
bar1  3
bar2  1
bar3  3
bar4  2

but what I'm trying to achieve is have a table where I know how many "bars" have a barcount of 1, how many have a barcount of 2 times, how many have a barcount of 3etc.

The restult I need is this, to make it simple:

barcount occurences
--------+-----------
  1          1
  2          1
  3          2

Is it possible to do this in a single SQL query, or would I have to rely on some code ?

Aucun commentaire:

Enregistrer un commentaire