May 12, 2010 at 9:29 am
Ok, so I know that I'm going to feel real dumb when I see the answer to this, but coffee doesn't seem to be working for me this morning. So, I wrote this query:
SELECT UNITID, COUNT(UNITID) AS TOTAL
FROM IMSV7.COMP
WHERE COMPTYPE = '38' and TOTAL > 1
GROUP BY UNITID
Of course it doesn't work, because TOTAL isn't a column. So, then I wrote it like this:
SELECT UNITID, COUNT(UNITID) AS TOTAL
FROM IMSV7.COMP
WHERE COMPTYPE = '38' and COUNT(UNITID) > 1
GROUP BY UNITID
Of course that doesn't work, because an aggregate can't be in a where clause. So, how do I limit my query to anything greater than 1?
Thanks in advanced.
Jordon
May 12, 2010 at 9:40 am
SELECT UNITID, COUNT(UNITID) AS TOTAL
FROM IMSV7.COMP
WHERE COMPTYPE = '38'
GROUP BY UNITID
HAVING COUNT(UNITID) > 1
May 12, 2010 at 9:42 am
Perfect! Thank you! I forgot about the Having clause!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply