Need multiple COUNTs with different criteria

I've got a query that's giving me an accurate count based on a searchable date range.  What I'd like to add is a year-to-date count as well -- I can do this with two different queries and then combine them programatically, but I'm sure there's a way to do this in SQL directly but I have no idea how to do so.  Here are the two queries that I've got so far:

SELECT pmUser.userFName, pmUser.userLName, count(woID) as woCount
FROM tblWO
JOIN tblAssetContractLink ON tblWO.assetContractLinkID = tblAssetContractLink.assetContractLinkID
JOIN tblAssets ON tblAssetContractLink.assetID = tblAssets.assetID
JOIN tblSpace ON tblAssets.spaceID = tblSpace.spaceID
JOIN tblTeamMember pmTM ON tblSpace.coID = pmTM.coID AND pmTM.teamPositionID = 1
JOIN tblUsers pmUser ON pmTM.userID = pmUser.userID
JOIN tblTeamMember secTM ON tblSpace.cOID = secTM.coID AND secTM.userID = 9
WHERE woCreatedDate BETWEEN '5/1/2009' AND '5/30/2009'
GROUP BY userFName, userLName
ORDER BY woCount DESC, userLName ASC

SELECT pmUser.userFName, pmUser.userLName, count(woID) as woCount
FROM tblWO
JOIN tblAssetContractLink ON tblWO.assetContractLinkID = tblAssetContractLink.assetContractLinkID
JOIN tblAssets ON tblAssetContractLink.assetID = tblAssets.assetID
JOIN tblSpace ON tblAssets.spaceID = tblSpace.spaceID
JOIN tblTeamMember pmTM ON tblSpace.coID = pmTM.coID AND pmTM.teamPositionID = 1
JOIN tblUsers pmUser ON pmTM.userID = pmUser.userID
JOIN tblTeamMember secTM ON tblSpace.cOID = secTM.coID AND secTM.userID = 9
WHERE woCreatedDate BETWEEN '1/1/2009' AND '5/30/2009'
GROUP BY userFName, userLName
ORDER BY woCount DESC, userLName ASC

What I'd like to get is a way to display 0 for woCount (the one using the monthly search range) since it's possible that a particular user would have a 0 in that field but still have a greater number for the year-to-date number.

Thanks very much in advance!
LVL 8
saoirse1916Asked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
try the code below
SELECT pmUser.userFName, pmUser.userLName, 
   SUM(CASE WHEN woCreatedDate BETWEEN '1/1/2009' AND '5/30/2009' THEN 1 ELSE O END) woCount1,
   SUM(CASE WHEN woCreatedDate BETWEEN '5/1/2009' AND '5/30/2009' THEN 1 ELSE O END) woCount2
FROM tblWO
JOIN tblAssetContractLink ON tblWO.assetContractLinkID = tblAssetContractLink.assetContractLinkID
JOIN tblAssets ON tblAssetContractLink.assetID = tblAssets.assetID
JOIN tblSpace ON tblAssets.spaceID = tblSpace.spaceID
JOIN tblTeamMember pmTM ON tblSpace.coID = pmTM.coID AND pmTM.teamPositionID = 1
JOIN tblUsers pmUser ON pmTM.userID = pmUser.userID
JOIN tblTeamMember secTM ON tblSpace.cOID = secTM.coID AND secTM.userID = 9
WHERE woCreatedDate BETWEEN '5/1/2009' AND '5/30/2009'
GROUP BY userFName, userLName
ORDER BY woCount DESC, userLName ASC

Open in new window

0
 
ralmadaCommented:
Can you clarify a bit what you are looking for? Maybe posting some sample data and expected results will help. Anyhow, you can have a conditional count using SUM instead, like this:
SELECT
...
SUM(CASE WHEN woCreatedDate BETWEEN '5/1/2009' AND '5/30/2009' THEN 1 else 0 end) as monthcount,
count(woID) as woCount
FROM tblWO
JOIN tblAssetContractLink ON tblWO.assetContractLinkID = tblAssetContractLink.assetContractLinkID
JOIN tblAssets ON tblAssetContractLink.assetID = tblAssets.assetID
JOIN tblSpace ON tblAssets.spaceID = tblSpace.spaceID
JOIN tblTeamMember pmTM ON tblSpace.coID = pmTM.coID AND pmTM.teamPositionID = 1
JOIN tblUsers pmUser ON pmTM.userID = pmUser.userID
JOIN tblTeamMember secTM ON tblSpace.cOID = secTM.coID AND secTM.userID = 9
WHERE woCreatedDate BETWEEN '1/1/2009' AND '5/30/2009'
GROUP BY userFName, userLName
ORDER BY woCount DESC, userLName ASC

Open in new window

0
 
mohan_sekarCommented:
How about using UNION?

SELECT pmUser.userFName, pmUser.userLName, count(woID) as 'monthlycount', 0 as 'yeartodatecount'
FROM tblWO
<WHERE clause>
UNION
SELECT pmUser.userFName, pmUser.userLName, 0 as 'monthlycount', count(woID) as 'yeartodatecount'
FROM tblWO
<WHERE clause>

0
 
saoirse1916Author Commented:
That did it -- had to make two tweaks, but now it's working great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.