MySQL: looking to SUM these UNIONs together

Question

OK, my head hurts...!

This beautiful MySQL query:

(SELECT mtwitterfollowers AS twitfollow FROM `media` WHERE media.id=1)
UNION
(SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN positions ON position_id = positions.id WHERE peoplejoin.media_id = 1)
UNION 
(SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN networkjoin ON networkjoin.network_id = peoplejoin.network_id LEFT JOIN positions ON position_id = positions.id WHERE networkjoin.media_id = 1)

...returns three rows of pretty numbers.

Ideally, I'd like this query to return all three "twitfollow" results, SUMmed together.

However, putting a SUM round them gives me an error about "every derived table must have its own alias", and I'm a little confused as to quite how to do solve that.

(Of course, I could just sum the results in PHP; but I am assuming that it's quicker to do this using the MySQL server. Would I be right?)

Answer 1

Use your entire query as the FROM clause of another query:

SELECT SUM(twitfollow) FROM (
    (SELECT mtwitterfollowers AS twitfollow FROM `media` WHERE media.id=1)
    UNION ALL
    (SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN positions ON position_id = positions.id WHERE peoplejoin.media_id = 1)
    UNION ALL
    (SELECT SUM(twitterfollowers) AS twitfollow FROM people LEFT JOIN peoplejoin ON peoplejoin.people_id = people.id LEFT JOIN networkjoin ON networkjoin.network_id = peoplejoin.network_id LEFT JOIN positions ON position_id = positions.id WHERE networkjoin.media_id = 1)
) t1

I also changed your UNION to UNION ALL as you probably don't want to remove rows just because the sum from one table is equal to the sum from another table.