Conditionally Summing the same Column multiple times in a single select statement?
- by btollett
I have a single table that shows employee deployments, for various types of deployment, in a given location for each month:
ID | Location_ID | Date | NumEmployees | DeploymentType_ID
As an example, a few records might be:
1 | L1 | 12/2010 | 7 | 1 (=Permanent)
2 | L1 | 12/2010 | 2 | 2 (=Temp)
3 | L1 | 12/2010 | 1 | 3 (=Support)
4 | L1 | 01/2011 | 4 | 1
5 | L1 | 01/2011 | 2 | 2
6 | L1 | 01/2011 | 1 | 3
7 | L2 | 12/2010 | 6 | 1
8 | L2 | 01/2011 | 6 | 1
9 | L2 | 12/2010 | 3 | 2
What I need to do is sum the various types of people by date, such that the results look something like this:
Date | Total Perm | Total Temp | Total Supp
12/2010 | 13 | 5 | 1
01/2011 | 10 | 2 | 1
Currently, I've created a separate query for each deployment type that looks like this:
SELECT Date, SUM(NumEmployees) AS "Total Permanent"
FROM tblDeployment
WHERE DeploymentType_ID=1
GROUP BY Date;
We'll call that query qSumPermDeployments. Then, I'm using a couple of joins to combine the queries:
SELECT qSumPermDeployments.Date, qSumPermDeployments.["Total Permanent"] AS "Permanent"
qSumTempDeployments.["Total Temp"] AS "Temp"
qSumSupportDeployments.["Total Support"] AS Support
FROM (qSumPermDeployments LEFT JOIN qSumTempDeployments
ON qSumPermDeployments.Date = qSumTempDeployments.Date)
LEFT JOIN qSumSupportDeployments
ON qSumPermDeployments.Date = qSumSupportDeployments.Date;
Note that I'm currently constructing that final query under the assumption that a location will only have temp or support employees if they also have permanent employees. Thus, I can create the joins using the permanent employee results as the base table. Given all of the data I currently have, that assumption holds up, but ideally I'd like to move away from that assumption.
So finally, my question. Is there a way to simplify this down to a single query or is it best to separate it out into multiple queries - if for no other reason that readability.
