CODEWITHSUNDEEP
sqlsqlite
brucezepplin
brucezepplin

Reputation: 9792

sqlite count and group every column in one query

I have the following table:

SCORE1    SCORE2    SCORE3
P         P         F
P         P         T
D         P         F
D         D         T
P         P         F
P         D         T

Where I want to count and group each column so I get the number of P and D for SCORE1 and SCORE2, as well as the number of T and F for SCORE3.

I though I may have done it like this

select SCORE1, count(SCORE1) as SCORE1_counts,
       SCORE2, count(SCORE2) as SCORE2_counts,
       SCORE3, count(SCORE3) as SCORE3_counts,
from mytable
group by SCORE1,SCORE2,SCORE3.

But cleary this will return a row for every unique combination of the columns in the group, where really I want something like:

SCORE1    SCORE1_counts    SCORE2    SCORE2_counts    SCORE3    SCORE3_counts
P         4                P         4                T         3
D         2                D         2                F         3

Is what I want even possible with just one sqlite query?

Upvotes: 0

Views: 381

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I think you are best off getting the values in rows, not columns. Does this do what you want:

select 'SCORE1' as which, count(*) as counts,
from mytable
group by Score1
union all
select 'SCORE2' as which, count(*) as counts,
from mytable
group by Score2
union all
select 'SCORE3' as which, count(*) as counts,
from mytable
group by Score3;

Upvotes: 2

Related Questions