Grouping by month from a date with nested aggregations | SQL

Viewed 41

I have a seemingly strange grouping error in Teradata. The query is calculating daily payment balance, and finding the smallest payment of each month, then aggregating by months. The issue arises when Im trying to group by months so I dont have duplicates like so:

Customer Currency Date_Period Amount
204,117,901 EUR 30/09/2021 0.06
204,117,901 EUR 30/09/2021 0.06
204,117,901 EUR 30/11/2021 1.07
204,117,901 EUR 30/11/2021 1.07

Instead, Im trying to get this:

Customer Currency Date_Period Amount
204,117,901 EUR 30/09/2021 0.06
204,117,901 EUR 30/06/2021 0.76
204,117,901 EUR 30/11/2021 1.07
204,117,901 EUR 31/01/2022 2.25

However, if i dont create a CTE or inline view, the group by causes 2 types of errors, either its nested aggregations not allowed in group by, or not all non-aggregated columns are present in group by (even though seemingly i select everything outside min() function). What would be the best way to do this? Code snippet without the group_by clause below.

select 
table1.Customer_Id  
, table1.Currency
, table1.Date_Period --end-of-month dates e.g. '30/11/2021' date type
-- , table1.Summary_Date --just for example what was used in partition clause, this is an every-day date e.g. '4/11/2021'
, min((case 
when table1.Criterion1 = supporttable1.Criterion1 
then cast((table2.Balance_Amount - table2.Reserved_Balance_Amount)as float)* cast(table3.Currency_Rate as float) 
else cast(table2.Balance_Amount as float) * cast(table3.Currency_Rate as float) end )) --over (partition by table1.Customer_id, extract(month from table2.Summary_Date) order by extract (month from table2.Summary_Date) asc) 
--does the job but does not remove duplicate rows
FROM table3 
inner join (( table1 full join supporttable1 on table1.Criterion1 = supporttable1.Criterion1
) 
inner join table2 on table1.customer_id = table2.customer_id 
) 
on table1.date_period = table3.date_period
-- GROUP BY table1.Customer_ID,   table1.Currency, table1.Date_Period) --gives back only minimum value of all period, where i need minimum of each month
0 Answers
Related