Counting without using Subqueries

[Cris987]

Hi, I have a table call userTagLogs with 3 columns

 

username, itemid, tagid

 

I would like to select the rows of this table along with the columns 'singleTagCount', which has the count of the rows with the same tagid and item id, and 'totTagCount', which has the count of the rows with the same itemid.

 

Right now, I have

SELECT username AS thisusername, tagid AS thistagid, itemid AS thisitemid, (

SELECT COUNT( tagid )
FROM userTagLogs
WHERE tagid = thistagid
AND itemid = thisitemid
GROUP BY itemid
) AS singleTagCount, (

SELECT COUNT( tagid )
FROM userTagLogs
WHERE itemid = thisitemid
GROUP BY itemid
) AS totTagCount
FROM `userTagLogs` 

 

It works ( I think), but it seems really ugly and unnecessary. Is there any way to simplify this?

[fenway]

Not really...