Select + count different parameters

Question

We have 2 columns in the first Site_ID (any, but repeated), in the second Level (0 or 1)

well, an example
123.0
666.1
967.0
666.0

In the end, I want to get (Site_id / count (Level = 0) / count (Level = 1)), that is, from the example above, we get

123,1,0
666,1,1
967,1,0

Vikolyada Vikolyada 1,359 6 silver marks 16 bronze marks · Answer 1 · 2017-08-08T11:01:29

Need to use CASE inside COUNT

 SELECT Site_id, COUNT(CASE Level WHEN 0 THEN 1 END) AS Level_0, COUNT(CASE Level WHEN 1 THEN 1 END) AS Level_1 FROM Table GROUP BY Site_id;

Akina Akina 15.2k 1 golden mark 10 silver marks 30 bronze marks · Answer 2 · 2017-08-08T11:34:21

 SELECT Site_id, SUM(Level = 0) AS Level_0, SUM(Level = 1) AS Level_1 FROM DataTable GROUP BY Site_id;

Akina

15.2k 1 golden mark 10 silver marks 30 bronze marks

Please describe the idea of how the SUM(Level = 0) construction works - mymedia
@mymedia In strict accordance with the documentation: the values TRUE and FALSE are merely aliases for 1 and 0, respectively . - Akina

|

2 answers 2