How to add together the results of several subqueries?

mysql, sql

Solution

Wrap it into a subquery:

SELECT  *,
        bookreviews + recipereviews AS totalreviews
FROM    (
        SELECT  users.*,
                (SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles,
                (SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) as bookreviews,
                (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as recipereviews
        FROM    users   
        ) q

Problem

I am running a MySQL query to rank my site's users according to the number of book reviews and recipe reviews they have contributed. After initial issues with a multiple JOIN query, I've switched to a series of subqueries, which is much, much faster. However, although I can extract the numbers of reviews from each member, I can't figure out how to add them together so I can sort by the total number. Here's the current query: ``` SELECT users.*, (SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles, (SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) as bookreviews, (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as recipereviews FROM users ``` I need to add together bookreviews and recipereviews to get 'reviewtotals'. MySQL won't allow you to use simple syntax to do calculations on aliases, but I presume there's another way to do this??

Original source

Related problems