I want a result set that includes all categories and their counts, even if one or more counts are 0.
Table: Numbers
| id | number |
| ------------ | ------------ |
| 0 | 2 |
| 1 | -1 |
| 2 | 1 |
What I've tried:
SELECT sign,
COUNT(*) sign_count
FROM (SELECT CASE WHEN number < 0 THEN 'negative'
WHEN number > 0 THEN 'positive'
ELSE 'neither'
END AS sign
FROM Numbers) n
GROUP BY sign
What I get:
sign | sign_count |
---|---|
negative | 1 |
positive | 2 |
What I want:
sign | sign_count |
---|---|
negative | 1 |
neither | 0 |
positive | 2 |
I understand that none of the values in Numbers
corresponds to neither
, but then how do I group by the null category? I have also searched around and tried self joins with Numbers
on the id column, as well as created a separate Signs
table consisting of all three signs to perform outer joins on, but neither gives what I'm looking for.
@ahmed Table: Signs
| sign |
| ------------ |
| negative |
| neither |
| positive |
Query including left join:
SELECT s.sign,
COUNT(*) sign_count
FROM (SELECT CASE WHEN number < 0 THEN 'negative'
WHEN number > 0 THEN 'positive'
ELSE 'neither'
END AS sign
FROM Numbers) n
LEFT JOIN Signs s ON n.sign = s.sign
GROUP BY s.sign
Result of above:
sign | sign_count |
---|---|
negative | 1 |
positive | 2 |
If replace LEFT
with RIGHT
in the query, result:
sign | sign_count |
---|---|
negative | 1 |
neither | 1 |
positive | 2 |
which is wrong.
5条答案
按热度按时间aurhwmvo1#
Yet another way of doing this is by
Output:
Check the demo here .
ztigrdn82#
This might be an option:
Query:
Results:
See it working on sqlfiddle.
gab6jxml3#
You can write conditions first(with cte) and then group based on those conditions
Demo
w1jd8yoj4#
Since you want all the signs values, you need to have a table with all of them in it. You can do a table valued subquery like the above or an actual or temporary table. But the idea is that if the value doesn't exist in your data, you are not going to get it in your output. Since the neither value is not in your data, you need to create a table that has it. Then we join to your actual table based on the sign of the number and get a count.
To explain the sum vs count thing, since this is outer join you'll get rows with Numbers.number equal to NULL for the neither case. So if you count them you get 1 instead of the desired 0. So I changed it to give each row a value of 0 or 1 and take the sum. So for the rows with NULL number I give them that value of 0 so they aren't counted and for the others a value of 1 so they are. If you had actual NULL values in your numbers table, you'd have to do the null check on a field that always has data and no nulls.
Edit: changed from count approach to sum to account for missing rows in join
zxlwwiss5#
This is a way to do it using
CASE
expression andUNPIVOT
:Will return :
Then we unpivot it using this query :
Result :
Demo here