SQL：具有不同条件的多个计数语句 [英] SQL: Multiple count statements with different criteria

问题描述

我只是想知道是否有办法使用一个查询从一个表中获得两个单独的计数总数？也就是说，使用类似于以下内容的表，我想检索每个代码（不同）并显示状态总数不等于X或D，然后再增加一列以显示状态总数等于到X或D，并且取消日期大于给定日期（例如最近的14天）。

I was just wondering if there is any way to get two separate "count" totals from a table using one query? That is, using a table similar to the following I would like to retrieve each code (distinct) and show the total number of status' NOT equal to X or D, and then have an additional column that shows the total number of status' equal to X or D and the cancel date is greater than a given date (say, the last 14 days).

表格：

Code:  Status  Cancel_Date
-----------------------------------
AAA    X       2012-02-01
AAA
BBB    X       2012-02-01
AAA    D       2012-01-01
AAA
BBB    
BBB    D       2012-02-01
BBB    X       2012-01-01

示例结果（基于上述数据）：

Example result (based on the above data):

Code:  TotalNotXorD     TotalXorD
------------------------------------
AAA    2                1
BBB    1                2

TotalNotXorD：例如

TotalNotXorD: e.g.

select code, count(*) 
from table 
where status not in('X','D') 
group by code

TotalXorD分组：例如

TotalXorD: e.g.

select code, count(*) 
from table 
where status in('X','D') 
  and cancel_date >= '2012-02-01' 
group by code

我看过做子查询等，但是我似乎无法获得所需的结果。

I have looked at doing subqueries etc. but I can't seem to get the results I need.

有什么想法吗？

谢谢。

推荐答案

SELECT  a.code,
        COALESCE(b.totalNotXorD, 0 ) totalNotXorD,
        COALESCE(c.totalXorD, 0 ) totalXorD,
FROM    (SELECT DISTINCT Code FROM tableName) a
        LEFT JOIN
        (
            select code, count(*) totalNotXorD
            from table 
            where status not in('X','D') 
            group by code
        ) b ON a.code = b.code
        LEFT JOIN
        (
            select code, count(*) totalXorD
            from table 
            where status in('X','D') 
              and cancel_date >= '2012-02-01' 
            group by code
        ) c ON a.code = c.code

或仅执行 Case

SELECT  Code,
        SUM(CASE WHEN status NOT IN ('X','D') OR status IS NULL THEN 1 ELSE 0 END) TotalNotXorD,
        SUM(CASE WHEN status IN ('X','D') AND cancel_date >= '2012-02-01' THEN 1 ELSE 0 END) TotalXorD  
FROM    tableName
GROUP   BY Code

这篇关于SQL：具有不同条件的多个计数语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！