How to add together the results of several subqueries?

Question

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??

Answer 1

You tried the following?

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) +
   (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID)) as reviewtotal
FROM users   

Answer 2

Two options:

Option 1:Ugly is hell, and probably slow (depends on the query cache):

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
   (SELECT count(*) FROM book_reviews WHERE book_reviews.user_id = users.ID) + (SELECT count(*) FROM recipe_reviews WHERE recipe_reviews.user_id = users.ID) as reviewtotals
FROM users   

Option 2: Save the results to a temporary table and then query this table

Perhaps this will work (haven't tried it)

SELECT *, bookreviews+recipereviews as reviewtotals 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) u  

Answer 3

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

Answer 4

If you want to be safe and fast, do it like this:

SELECT users.*
,      titles.num                            titles
,      book_reviews.num                      book_reviews
,      recipe_reviews.num                    recipe_reviews
,      book_reviews.num + recipe_reviews.num total_reviews
FROM      users   
LEFT JOIN (
          SELECT   user_ID, count(*) AS num
          FROM     bookshelf
          GROUP BY user_ID
          ) as titles
ON        users.ID = titles.user_ID
LEFT JOIN (
          SELECT   user_ID, count(*) AS num
          FROM     book_reviews
          GROUP BY user_ID
          ) as book_reviews
ON        users.ID = reviews.user_ID
LEFT JOIN (
          SELECT   user_ID, count(*) AS num
          FROM     recipe_reviews
          GROUP BY user_ID
          ) as recipe_reviews
ON        users.ID = recipes.user_ID

If you want to stick to the subqueries in the SELECT list, and want it to be safe, take a look at Quassnoi's solution.

If you like to live a bit dangersouly and still want a fast result, you can use user-defined variables. I predict it will be safe in this very particular case:

SELECT users.*,
       (SELECT count(*) FROM bookshelf WHERE bookshelf.user_id = users.ID) as titles,
       @bookreviews:=(
           SELECT count(*) 
           FROM book_reviews 
           WHERE book_reviews.user_id = users.ID
       ) as bookreviews,
       @recipereviews:=(
           SELECT count(*) 
           FROM recipe_reviews 
           WHERE recipe_reviews.user_id = users.ID
       ) as recipereviews,
       @bookreviews + @recipereviews as total_reviews
FROM users