asked on

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!

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

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

mohan_sekar

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>

saoirse1916

ASKER

That did it -- had to make two tweaks, but now it's working great!