sql - 具有条件的同一行上的多个计数

Question

我正在尝试计算表中数据的汇总 View ，目前查询有效，但效率不高，因为我的表最终将持有 10,000 多个合约。我还需要按相同的日期过滤每个，我意识到我可以在每个计数的 where 语句中执行此操作，但这不是很有效。

如果我可以对字段而不是选择进行操作，那么最后的最终计算也会容易得多。

(SELECT COUNT (*) FROM Contracts WHERE contractWon = 1) Won,
(SELECT COUNT (*) FROM Contracts WHERE contractPassedToCS = 1) PassedtoCS,
(SELECT COUNT (*) FROM Contracts WHERE contractPassedToCS = 0) as OutstandingWithSales,
(SELECT COUNT (*) FROM Contracts WHERE contractIssued = 1) as ContractsIssued,
(SELECT COUNT (*) FROM Contracts WHERE contractReturned = 1) as ContractsReturned,
(CONVERT(decimal, (SELECT COUNT (*) FROM Contracts WHERE contractReturned = 1)) /         CONVERT(decimal, (SELECT COUNT (*) FROM Contracts WHERE contractIssued = 1))) * 100 as '% Outstanding'

我知道可能需要一些加入，但我有点困惑。

谢谢。

Answer 1

SELECT
    SUM(contractWon) AS Won,
    SUM(contractPassedToCS ) AS PassedtoCS,
    SUM(1-contractPassedToCS) AS OutstandingWithSales,
    SUM(contractIssued) AS contractIssued ,
    SUM(contractReturned) AS contractReturned,
    100.0 * SUM(contractReturned) / SUM(contractIssued) AS '% Outstanding'
FROM
    Mytable

无法聚合的“位”数据类型的替代公式。例如，如果列是 int，则上述查询有效

    --Either CAST first to a type that aggregates...
    SUM(CAST(contractWon AS int)) AS Won,

    -- .. or rely on COUNT ignores NULL values
    COUNT(CASE contractWon WHEN 1 THEN 1 ELSE NULL END) AS Won,