how can I use function SUM() inside of SUM function in Presto SQL?

Question

I m trying to get SUM of two numbers by following following logic: x = y + j based on this logic I wrote presto query using SUM() function in order to get SUM of x and y but I got Presto syntax error

SELECT
 SUM(sum(CASE
        WHEN source = 'x' THEN num_tasks
        ELSE 0 
    END) + sum(CASE WHEN source = 'y' THEN num_tasks ELSE 0  END)) as total

Error notice Presto query failed. Error: SYNTAX_ERROR: Cannot nest aggregations inside aggregation 'sum': ["sum"((CASE WHEN (source = 'y') THEN count ELSE 0 END)), "sum"((CASE WHEN (source = 'x') THEN num_tasks ELSE 0 END))]

It looks like you can't use Sum within Sum the way you want according to the SQL documentation. Instead, try something like SUM(SELECT FIELD_1, FIELD_2 from TABLE_1, TABLE_2). Then you can pull back the columns you want to sum up all at once — Michael Platt

Dain Sundstrom Dain Sundstrom · Accepted Answer · 2019-04-10T18:00:58

I'm not sure I understand what you are trying to do, but I think the following changes may help:

Use an IN statement in CASE WHEN: source in ('x', 'y')
Use IF instead of CASE: if(source in ('x', 'y'), num_tasks, 0)
I believe you only need one SUM call.

Putting this all together:

SELECT SUM(if(source in ('x', 'y'), num_tasks, 0))

Alternatively, you can use the SUM with FILTER syntax:

SELECT SUM(num_tasks) filter (where source in ('x', 'y'))

how can I use function SUM() inside of SUM function in Presto SQL?

1 Answers