Search code examples
sqlteradatateradata-sql-assistant

Teradata - Cannot nest aggregate operations with case

I am trying to run the following query:

select BI_NROCTA,
MAX(CASE WHEN FECHA_REGISTRO='2019-12-01' THEN COUNT(TRAMO) ELSE 0 END) "2019-12-01",
MAX(CASE WHEN FECHA_REGISTRO='2019-12-02' THEN COUNT(TRAMO) ELSE 0 END) "2019-12-02",
MAX(CASE WHEN FECHA_REGISTRO='2019-12-03' THEN COUNT(TRAMO) ELSE 0 END) "2019-12-03"

from DL_RG_ANALYTICS.SH_MORA_OSC_2 
WHERE BI_NROCTA='0004890702108691953'
GROUP BY BI_NROCTA,TRAMO,FECHA_REGISTRO

But the query error: Cannot nest aggregate operations.

Should I use another query type?

Solution

  • Is this what you're looking for?

    select
BI_NROCTA,
max("2019-12-01"),
...
from
(
select
BI_NROCTA,
count (CASE WHEN FECHA_REGISTRO='2019-12-01' THEN (TRAMO) ELSE null END) as "2019-12-01",
...
from DL_RG_ANALYTICS.SH_MORA_OSC_2 
WHERE BI_NROCTA='0004890702108691953'
GROUP BY BI_NROCTA --other group by columns not needed
) t
group by
BI_NROCTA