January 29, 2009 at 8:12 am
Hello,
I am trying to use the following code to extract only the groups that have less than 3 members and affiliates total. What syntax do I need to use HAVING COUNT to do this? As it currently is it will return a group that has less than 3 affiliates even if it has more than 2 members or will return a group that has less than 3 members even if it has more than 2 affiiliates. I need the syntax to tell it to only return the groups that have less than 3 members AND affiiliates combined.
Thank you
SELECT a.groupid, b.name, COUNT(a.groupid) AS 'Total #'
FROM subjects a
INNER JOIN Group b
ON a.groupid = b.groupid
GROUP BY a.groupid, b.name, a.subjecttype
HAVING COUNT(a.groupid) < 3 AND subjecttype IN ('member','affiliate') AND a.groupid like '03%'
ORDER BY b.name
January 29, 2009 at 8:25 am
This should do it:SELECT a.groupid, b.name, COUNT(a.groupid) AS 'Total #'
FROM subjects a
INNER JOIN Group b ON a.groupid = b.groupid
WHERE subjecttype IN ('member','affiliate') AND a.groupid like '03%'
GROUP BY a.groupid, b.name
HAVING COUNT(a.groupid) < 3
ORDER BY b.name
You had subjecttype in the GROUP BY. The combination of all the GROUP BY items is made unique. I moved the other parts of the HAVING clause apart from the COUNT to the WHERE clause. I know at least that you don't need to reference those in the GROUP BY. I think that's not the case with what's in HAVING.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 29, 2009 at 8:39 am
Much better! Thanks Ronald!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply