MySQL—SUM multiple COUNTs?

Question

I have to get some data that if possible should be done in one query—if push comes to shove I could always write some PHP logic to do it in two, but I’m thinking that it would be possible to do it in a single query. I don’t know how!

Here is what I want to do:

Generate a listing that displays the total number of posts by an author. Therefore I have to COUNT three different columns from three different tables (reviewsID FROM reviews, featuresID FROM features & newsID FROM news) and then SUM them together to provide a total figure. These will have to be limited with a WHERE userID = THE_ID to have that single user.

At the same time, I want a simpler query that gets a couple things about him/her from the database—userRealName and userLevel.

SELECT userRealName, userLevel FROM user WHERE (userLevel = 'a' OR userLevel = 'e' OR userLevel ='r')

The final table should look something like this:

*userRealName*userLevel*articles*
|------------|---------|--------|
|The user    |e        |87      |
|Another user|r        |34      |

Is this possible?

Answer 1

This is really hard to do without MySQL here to test it, but I think something like this would work:

SELECT u.userRealName, u.userLevel, COUNT(t.articleID)
FROM user u,
(
    SELECT userID, reviewsID articleID FROM reviews
    UNION ALL
    SELECT userID, featuresID articleID FROM features
    UNION ALL
    SELECT userID, newsID articleID FROM news
) t
WHERE u.userID = t.userID
   AND (u.userLevel = 'a' OR u.userLevel = 'e' OR u.userLevel ='r')
GROUP BY u.userRealName, u.userLevel