SQL Server SELECT CASE expression, COUNT(*) aggregate to include 0 when no rows match a case

mnowg1ta  于 2023-05-21  发布在  其他
关注(0)|答案(5)|浏览(102)

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:

signsign_count
negative1
positive2

What I want:

signsign_count
negative1
neither0
positive2

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:

signsign_count
negative1
positive2

If replace LEFT with RIGHT in the query, result:

signsign_count
negative1
neither1
positive2

which is wrong.

aurhwmvo

aurhwmvo1#

Yet another way of doing this is by

  • cross-joining your sign string values with your table
  • counting conditionally your values according to the required sign
SELECT sign,
       COUNT(CASE WHEN sign = 'positive' AND number > 0 THEN 1
                  WHEN sign = 'negative' AND number < 0 THEN 1
                  WHEN sign = 'neither' AND number = 0 THEN 1 END) AS sign_count
FROM       (VALUES('positive'),('negative'),('neither')) AS signs(sign)
CROSS JOIN Numbers
GROUP BY sign

Output:

signsign_count
positive2
negative1
neither0

Check the demo here .

ztigrdn8

ztigrdn82#

This might be an option:

  • Make a query for each condition and union the queries using the "UNION ALL" keywork for mix the results of the queries in a single result.

Query:

SELECT 'negative' sign, COUNT(1) sign_count
FROM Numbers
WHERE number < 0
UNION ALL
SELECT 'neither' sign, COUNT(1) sign_count
FROM Numbers
WHERE number = 0
UNION ALL
SELECT 'positive' sign, COUNT(1) sign_count
FROM Numbers
WHERE number > 0;

Results:

signsign_count
negative1
neither0
positive2

See it working on sqlfiddle.

gab6jxml

gab6jxml3#

You can write conditions first(with cte) and then group based on those conditions

with _listcon as (

select  'negative'  as  sign,CAST(0x8000000000000000 AS bigint) /*min bigint*/ startNumber,-1 EndNumber union 
select  'positive'  as  sign,1 startNumber, CAST(0x7FFFFFFFFFFFFFFF AS bigint) /*max bigint*/   EndNumber union 
select  'neither'  as  sign, 0 startNumber,0 EndNumber 

)

select  sign, ISNULL( COUNT(a.number),0) sign_count
from Numbers a
right join _listcon b on a.number between startNumber and EndNumber
GROUP BY sign

Demo

w1jd8yoj

w1jd8yoj4#

SELECT signs.sign, 
       SUM(CASE WHEN Numbers.number IS NULL THEN 0 ELSE 1 END)
FROM (SELECT 'positive' AS [sign] 
      UNION ALL 
      SELECT 'negative' 
      UNION ALL 
      SELECT 'neither') AS signs
LEFT OUTER JOIN Numbers 
             ON (signs.sign = CASE WHEN number < 0 THEN 'negative' 
                                   WHEN number > 0 THEN 'positive' 
                                   ELSE 'neither' END)
GROUP BY signs.sign

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

zxlwwiss

zxlwwiss5#

This is a way to do it using CASE expression and UNPIVOT :

select count(case when number < 0 then 1 end) as 'negative',
       count(case when number > 0 then 1 end) as 'positive',
       count(case when number = 0 then 1 end) as 'neither'
from Numbers;

Will return :

negative    positive    neither
1           2           0

Then we unpivot it using this query :

with cte as (
    select count(case when number < 0 then 1 end) as 'negative',
           count(case when number > 0 then 1 end) as 'positive',
           count(case when number = 0 then 1 end) as 'neither'
    from Numbers
)
select sign, sign_count
from cte
unpivot
(
  sign_count
  for sign in (negative, positive, neither)
) u;

Result :

sign        sign_count
negative    1
positive    2
neither     0

Demo here

相关问题