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,
精彩评论