Workaround: Using LAG command inside SUM command

I have query which returns the following result:

 -- Get current int_type and compare with next int_type. If they are different, put 1. Then sum all results
select CASE WHEN int_type <> LAG(int_type) OVER (ORDER BY id) THEN 1 END as next_int_type
from data

Output:
0
0
0
1
1
1
0
0
0
0
0
1
0
0
0
1
0
1

The problem with using LAG command inside SUM command:

select SUM(CASE WHEN int_type <> LAG(int_type) OVER (ORDER BY id) THEN 1 END) as next_int_type
from data

Error: ERROR: aggregate function calls cannot contain window function calls

How can I sum above result in one query?

Workaround:

CREATE TEMP TABLE Temp AS
    select SUM(CASE WHEN int_type <> LAG(int_type) OVER (ORDER BY id) THEN 1 END) as next_int_type
    from data

select SUM(CASE WHEN int_type <> next_int_type THEN 1 ELSE 0 END) Total
from Temp ;

Output: 6


Source: sql

Leave a Reply