CODEWITHSUNDEEP
sqlsqlite
MLEV
MLEV

Reputation: 17

Formatting the results of a query

Let's say I have the following table:

first  second
A      1
A      1
A      2
B      1
B      2
C      1
C      1

If I run the following query:

select first, second, count(second) from tbl group by first, second

It will produce a table with the following information:

first  second  count(second)
A      1       2
A      2       1
B      1       1
B      2       1
C      1       2

How can I write the query so that I am given the information with the options from the second column as columns and the values for those columns being the count like this:

first  1  2
A      2  1
B      1  1
C      2  0

Upvotes: 0

Views: 35

Answers (1)

Lamak
Lamak

Reputation: 70648

You can use CASE:

SELECT  "first",
        SUM(CASE WHEN "second" = 1 THEN 1 ELSE 0 END) AS "1",
        SUM(CASE WHEN "second" = 2 THEN 1 ELSE 0 END) AS "2"
FROM tbl
GROUP BY "first"

Upvotes: 1

Related Questions