开发者

Multiple Counts On Same Row With Conditions

I am trying to calculate a summarised view of the data in my table, at the moment the query works but it isn't very efficient seeing as my table will eventually hold 10,000 + contracts. I also need to filter each by the same date, I realise I could do this in the where statement for each count but this isn't very efficient.

Also the final c开发者_JAVA技巧alculation at the end would be much easier if I could operate on fields rather than selects.

(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'

I understand there is probably some joining needed but I'm a little confused.

Thanks.


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

Alternate formulations for "bit" datatypes which can not be aggregated. If the columns are int, say, then the above query works

    --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,
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜