CODEWITHSUNDEEP
mysqlsumsubquery
Nick Budden
Nick Budden

Reputation: 641

MySQL: Sum of multiple subqueries

I need to order a MySQL query by the resulting SUM of multiple subqueries.

Here's some example code for what I'm trying to do:

SELECT  ...
        (SELECT SUM(
           (SELECT one_result ... LIMIT 1) as plays1,
           (SELECT one_result ... LIMIT 1) as plays2,
           (SELECT one_result ... LIMIT 1) as plays3
        )) as total_plays
FROM plays
ORDER BY total_plays

Basically, I need to run three subqueries that'll each return an integer.

I need to order the entire query by the SUM() of these integers.

When I try to run this query I get a syntax error.

Could someone let me know what the proper syntax is for summing multiple subqueries?

I've also already tried:

SELECT  ...
        (SELECT one_result ... LIMIT 1) as plays1,
        (SELECT one_result ... LIMIT 1) as plays2,
        (SELECT one_result ... LIMIT 1) as plays3
        SUM(plays1, plays3, plays3) as total_plays
FROM plays
ORDER BY total_plays

EDIT

@JoeC and @SATSON provided similar answers that solved this. Here's my (working) real query in case this helps anyone else get started on their own query:

````

SELECT  song.title as title,
        song.file_name as unique_name,
        song.artist as artist,
       (SELECT difficulty FROM chart WHERE id = song.easy_chart ORDER BY scoring_version ASC LIMIT 1) as easy_difficulty,
       (SELECT difficulty FROM chart WHERE id = song.hard_chart ORDER BY scoring_version ASC LIMIT 1) as hard_difficulty,
       (SELECT difficulty FROM chart WHERE id = song.master_chart ORDER BY scoring_version ASC LIMIT 1) as master_difficulty,
       (plays.easy_plays + plays.hard_plays + plays.master_plays) as total_plays
FROM song
INNER JOIN (SELECT parent_song_id,
               (SELECT global_plays ORDER BY scoring_version ASC LIMIT 1) as easy_plays,
               (SELECT global_plays ORDER BY scoring_version ASC LIMIT 1) as hard_plays,
               (SELECT global_plays ORDER BY scoring_version ASC LIMIT 1) as master_plays
       FROM chart) as plays
ON plays.parent_song_id = song.id
ORDER BY total_plays DESC
LIMIT 9
OFFSET 0

````

Upvotes: 1

Views: 2660

Answers (2)

JoeC
JoeC

Reputation: 1850

Ummm, what about

SELECT *, plays1 + plays2 + plays3 as total_play from 
(SELECT  ...
        (SELECT one_result ... LIMIT 1) as plays1,
        (SELECT one_result ... LIMIT 1) as plays2,
        (SELECT one_result ... LIMIT 1) as plays3
FROM plays)
ORDER BY total_plays

Upvotes: 2

Sathish
Sathish

Reputation: 4487

Try Like this

SELECT  sum(plays1) as total_plays from (
        (SELECT one_result as plays1  ... LIMIT 1)
        union all 
        (SELECT one_result as plays1 ... LIMIT 1 )
        union all
        (SELECT one_result as plays1 ... LIMIT 1)
      )
as  plays
ORDER BY total_plays

Upvotes: 1

Related Questions