How to SUM() multiple subquery rows in MySQL?

Question

Hi,

The question is simple: I want to do:

SELECT SUM((... a subquery that returns multiple rows with a single int value ...)) AS total;

How would I do that? I get an error saying that subquery returns more than one row. I need to have it in a subquery.

Answer 1

Couldn't you just do the aggregation within the subquery?

SELECT
    (SELECT SUM(...) ...) AS total,
    ...

(Untested!)

Answer 2

Here's an approach that should work for you:

SELECT SUM(column_alias)
FROM (select ... as column_alias from ...) as table_alias

And here's a specific dummy example to show the approach in action:

select sum(int_val) 
from (
  select 1 as int_val 
  union
  select 2 as int_val 
  union 
  select 3 as int_val
) as sub;