[thelist] MySQL multiple counts problem / help needed
Ian Goodyer
ig at meeting-place.co.uk
Mon Oct 24 15:29:35 CDT 2005
Hi guys,
I wonder if any of you wizzes can help. I have a database that contains a
users table. One of the fields in the table is sub (for subscription
status) and this contains an integer (0 for guests, 1 for full members, 2
for hidden members etc). The users table also contains a region field that
lists which user of the UK the user comes from and a name field containing
the users name.
I would like to set up a MYSQL query to display the number of guests
(sub=0), full members (sub=1) and hidden members (sub=2) for each site.
I can show the number of guests like this of course:
SELECT site, sub, COUNT( * ) AS guests
FROM users
WHERE sub = 0
GROUP BY site
And do something similar to show full members and hidden members. What I
really want though is to retrieve number of guests, full members and hidden
members at the same time.
I tried this:
SELECT users.site, COUNT( a.name ) AS guests, count( b.name ) AS full,
count(c.name) AS hidden
FROM users
LEFT JOIN users AS a ON users.sub=0
LEFT JOIN users AS b ON users.sub=1
LEFT JOIN users AS c ON users.sub=2
WHERE users.sub > -1
GROUP BY users.site
But it really upsets the server. It times out.
Can you see what I am trying to do?? Can you suggest a way of doing it?
Thanks,
Ian
More information about the thelist
mailing list