Alan Alan - 1 month ago 5
SQL Question

Is it possible to have some kind of a group query that groups on the number in a column?

I have a very simple table:

CREATE TABLE "Score"(
"Id" varchar primary key not null ,
"EnglishCount" int ,
"RomajiCount" int )


Is there a type of query that I could run that would show me:

how many rows have EnglishCount = 0,
how many rows have EnglishCount = 1,
how many rows have EnglishCount = 2,
how many rows have EnglishCount = 3,
how many rows have EnglishCount = 4,
etc ...


Here's the kind of output I am hoping to get:

Count Instances
0 1
1 2
3 1
4 5
5 2

Answer

You can use a group by clause to separate the result per distinct value of EnglishCount and then apply count(*) to each group:

SELECT   EnglishCount, COUNT(*)
FROM     Score
GROUP BY EnglishCount