[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