Using Count and Group Problem...

[Akumadevil]

Hi,
I have the following 2 tables:

-------------------------------

users (user_id, name)
sessions (user_id, paid_date)

-------------------------------

I want a query to show all users names who have more than 5 (paid_date != null)

So far I can group the sessions where paid_date != null which might give the following:

user_id, count(*)
1 7
2 10
3 2

But when I try my entire query:

select sessions.user_id, count(*) as abc
from sessions, users
where sessions.paid_date != 'null' and abc > 5 and sessions.user_id = users.user_id
group by sessions.user_id

It stuffs up on the abc > 5 part.

Any ideas?

 

[PHV]

To test the result of an aggregate function you must use the HAVING clause:
...
GROUP BY sessions.user_id
HAVING COUNT(*)>5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Akumadevil]

Thanks PHV,
problem solved.