Geek Answers Handbook

Multiple counts on one line with conditions

I am trying to calculate a generalized representation of the data in my table, the query is currently working, but it is not very efficient, since my table will ultimately contain 10,000+ contracts. I also need to filter each one by one date, I understand that I can do this in the where statement for each count, but this is not very efficient.

Also, the final calculation at the end would be much easier if I could work with fields and not with selections.

(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 that I probably need to join, but I'm a little confused.

Thanks.

+4
source share
1 answer
 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

Alternative formulations for “bits” of data types that cannot be aggregated. If the columns are int, let's say 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,
+4
source

More articles:


All Articles