Is it possible to have a WHERE clause while having a GROUP BY clause?
I thought when you use GROUP BY, you will be using HAVE instead of WHERE.

Thanks for the input

Enzo c",)
		

-----Original Message-----
From: skicamel@b... [mailto:skicamel@b...]
Sent: Thursday, November 14, 2002 3:33 PM
To: sql language
Subject: [sql_language] Re: Is it simple?


I think Enzo had the right approach.  I've modified it a bit, but the 
logic is there.  I had planned to write something to figure out the number 
of input variables to adjust the having clause, but I've got to get my 
butt to work.  Give this a shot...

SELECT Rooms, Count(software) as Cnt 
FROM tblRooms 
where software in ('ms word','ms visual studio')
GROUP BY Rooms
HAVING COUNT(Rooms) > 1

Dan

> Thanks for your thoughts Enzo and the rest but still nothing. 
I mean it looks so simple but when you actually look at it, it is almost
impossible.
It probably can't be done without a use of programming language.
And the answer is definetely not the types like:
select * from table where software ='MS WORD' OR software ='COREL' It will
return rooms that only have one but not the other. And of course the AND is
out of the question [dealing with different records]

My thoughts is to run one sql statement to get the list of rooms of the
first software,
another for the second software and then compare using PHP or something 
like
that and get the common.

-----Original Message-----
From: Enzo Zaragoza [mailto:enzaux@g...] 
Sent: 14 November 2002 07:35
To: sql language
Subject: [sql_language] Re: Is it simple?



SELECT Rooms, Count(Rooms) as RoomCnt 
FROM tblRooms 
GROUPBY Rooms, Software
HAVING Software='MS WORD' OR Software='MS VISUAL STUDIO' AND COUNT(Rooms) >
1

Try this one

Enzo c",)