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

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))]

2 Answers

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'))

Just as an addition to @Dain Sundstrom's answer: Using conditional summing

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

and filtering like

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

can even be combined. Why should I use this? Just imagine you had an issue in prod and one machine did write data with wrong factor (e.g. 1000x). Any price greater than 1000, but just for source 'x' or 'y'.

This could be fixed by

SELECT sum(if(price >= 1000000, price/1000, price)),0)
filter (where source in ('x', 'y')) as revenue

So I'd use a conditional IF statement if it's about manipulating the metric itself and filtering if it's about the input records to be processed. This way you stay open to add the other processing on top with minimal changes later on.