|
| |
|
|
CraigBFG
Posts: 211
Joined: 7/25/2003
From: UK
Status: offline
 |
Distinct count?? within a query - 1/16/2004 9:10:34
I have this query which gives me a list of categories and various counts against them. What I'd like to do is include an "address count", as the same address may be responsible for multiple calls. At the moment, when I do a count, the number of properties=the number of calls which may not be true.
SELECT tblDefCatType.DefCatType, tblDefCatType.DefCat, Count(tblDefect.DefID) AS Defects, tblDefCatType.DCTId, tblUsers.UId, Sum(Right([Result]<>'valid',1)) AS NV, Sum(Right([Status]='closed',1)) AS Closed, Count(tblDefect.AddID) AS CountOfAddID
FROM tblUsers RIGHT JOIN (tblDefStd INNER JOIN (tblDefCatType LEFT JOIN (tblResult RIGHT JOIN tblDefect ON tblResult.ResultId = tblDefect.ResultId) ON tblDefCatType.DCTId = tblDefect.DCTId) ON tblDefStd.DefStdId = tblDefCatType.DefStdId) ON tblUsers.UId = tblDefect.UId
GROUP BY tblDefCatType.DefCatType, tblDefCatType.DefCat, tblDefCatType.DCTId, tblUsers.UId
HAVING (((Count(tblDefect.DefID))>0) AND ((tblUsers.UId)<>17))
ORDER BY tblDefCatType.DefCat, Count(tblDefect.DefID) DESC;
Eg
Heating Not Working - Total Calls (5) - Total Closed (4) - Total NV (1) - Total Properties (2)
Essentially showing me that all the 5 calls came from 2 properties.
Please help
Thanks
Craig
|
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
