SELECT CASE 表达式，COUNT(*) 聚合函数，当没有行匹配到一个 case 时也包括 0。

huangapple
go评论68阅读模式
英文:

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

问题

以下是您要翻译的内容：

我想要一个包含所有类别及其计数的结果集，即使一个或多个计数为0。

表格：`Numbers`

|   id   | number |
| ------ | ------ |
|    0   |    2   |
|    1   |   -1   |
|    2   |    1   |

我尝试过的SQL查询如下：

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

我得到的结果如下：

sign sign_count
negative 1
positive 2 

我想要的结果如下：

```sql
|   sign   | sign_count |
| -------- | ---------- |
| negative |      1     |
| neither  |      0     |
| positive |      2     |

我理解在Numbers表中没有任何值对应于neither，但是如何按照空类别分组呢？我还尝试了在id列上与Numbers进行自连接，以及创建了一个包含所有三个标志的单独的Signs表来执行外连接，但都没有得到我想要的结果。

表格：Signs

sign
negative
neither
positive

包括左连接的查询如下：

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

上述查询的结果如下：

sign sign_count
negative 1
positive 2 

如果在查询中将`LEFT`替换为`RIGHT`，结果如下：

```sql
|   sign   | sign_count |
| -------- | ---------- |
| negative |      1     |
| neither  |      1     |
| positive |      2     |

这是错误的。


<details>
<summary>英文:</summary>

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&#39;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&#39;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.

</details>


# 答案1
**得分**: 2

以下是翻译好的内容：

另一种执行此操作的方法是通过以下步骤：
- 用您的符号字符串值与您的表进行交叉连接
- 根据所需的符号有条件地计数您的值

```sql
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

输出：

sign sign_count
positive 2
negative 1
neither 0

查看演示 这里。

英文:

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 = &#39;positive&#39; AND number &gt; 0 THEN 1
                  WHEN sign = &#39;negative&#39; AND number &lt; 0 THEN 1
                  WHEN sign = &#39;neither&#39; AND number = 0 THEN 1 END) AS sign_count
FROM       (VALUES(&#39;positive&#39;),(&#39;negative&#39;),(&#39;neither&#39;)) AS signs(sign)
CROSS JOIN Numbers
GROUP BY sign

Output:

sign sign_count
positive 2
negative 1
neither 0

Check the demo here.

答案2

得分: 1

以下是翻译好的部分：

这可能是一种选择：

  • 为每个条件制作一个查询，并使用“UNION ALL”关键字将这些查询联合起来，以将查询结果混合在单个结果中。

查询：

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;

结果：

sign sign_count
negative 1
neither 0
positive 2

在 sqlfiddle 上查看其运行情况。

英文:

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 &#39;negative&#39; sign, COUNT(1) sign_count
FROM Numbers
WHERE number &lt; 0
UNION ALL
SELECT &#39;neither&#39; sign, COUNT(1) sign_count
FROM Numbers
WHERE number = 0
UNION ALL
SELECT &#39;positive&#39; sign, COUNT(1) sign_count
FROM Numbers
WHERE number &gt; 0;

Results:

sign sign_count
negative 1
neither 0
positive 2

See it working on sqlfiddle.

答案3

得分: 1

选择 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
英文: 
SELECT signs.sign, 
       SUM(CASE WHEN Numbers.number IS NULL THEN 0 ELSE 1 END)
FROM (SELECT &#39;positive&#39; AS [sign] 
      UNION ALL 
      SELECT &#39;negative&#39; 
      UNION ALL 
      SELECT &#39;neither&#39;) AS signs
LEFT OUTER JOIN Numbers 
             ON (signs.sign = CASE WHEN number &lt; 0 THEN &#39;negative&#39; 
                                   WHEN number &gt; 0 THEN &#39;positive&#39; 
                                   ELSE &#39;neither&#39; 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

答案4

得分: 1

你可以首先编写条件（使用 cte），然后根据这些条件分组。

with _listcon as (
    select 'negative' as sign, CAST(0x8000000000000000 AS bigint) as startNumber, -1 as EndNumber union 
    select 'positive' as sign, 1 as startNumber, CAST(0x7FFFFFFFFFFFFFFF AS bigint) as EndNumber union 
    select 'neither' as sign, 0 as startNumber, 0 as EndNumber 
)

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

演示

英文:

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

with _listcon as (

select  &#39;negative&#39;  as  sign,CAST(0x8000000000000000 AS bigint) /*min bigint*/ startNumber,-1 EndNumber union 
select  &#39;positive&#39;  as  sign,1 startNumber, CAST(0x7FFFFFFFFFFFFFFF AS bigint) /*max bigint*/   EndNumber union 
select  &#39;neither&#39;  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

答案5

得分: 1

这是使用 CASE 表达式和 UNPIVOT 的一种方法：

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

将返回：

negative	positive	neither
1	        2	        0

然后我们使用以下查询来进行 unpivot 操作：

with cte as (
    select count(case when number &lt; 0 then 1 end) as 'negative',
           count(case when number &gt; 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;

结果为：

sign	    sign_count
negative	1
positive	2
neither	    0

演示链接

英文:

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

select count(case when number &lt; 0 then 1 end) as &#39;negative&#39;,
       count(case when number &gt; 0 then 1 end) as &#39;positive&#39;,
       count(case when number = 0 then 1 end) as &#39;neither&#39;
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 &lt; 0 then 1 end) as &#39;negative&#39;,
           count(case when number &gt; 0 then 1 end) as &#39;positive&#39;,
           count(case when number = 0 then 1 end) as &#39;neither&#39;
    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

huangapple
  • 本文由 发表于 2023年5月17日 23:41:32
  • 转载请务必保留本文链接：https://go.coder-hub.com/76273887.html
  • aggregate-functions
  • case
  • count
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定