[thelist] Grouping COUNT fields from multiple tables in mySQL
Jay Blanchard
jay.blanchard at niicommunications.com
Fri Feb 11 08:22:31 CST 2005
[snip]
SELECT user.name, COUNT(item.item_id) item_count, COUNT(stuff.stuff_id)
stuff_count
...
Is there a way to have the totals of more that one joined table (grouped
by the
same field)?
[/snip]
You may be able to use a SUM IF statement...(>>>>>not tested<<<<<)
SELECT user.name,
SUM(IF(user.name = item.owner_id, 1, 0)) AS ItemCount,
SUM(IF(item.owner_id = stuff.owner_id)) AS StuffCount
FROM user LEFT OUTER JOIN item
(ON user.name = item.owner_id)
LEFT OUTER JOIN stuff
(ON item.owner_id = stuff.owner_id)
GROUP BY user.name
More information about the thelist
mailing list