0

I have a requirement to find the average of a rate field. The average should be based know a date field, account number and product code. I need to use this query inside an insert..select statement.

Insert into tablename Rate Select Avg(rate..... From Tablenane

Example

Acctid | date | prodcode | rate

101 | 31-12-2023 | A100 | 0.045

101 | 31-12-2023 | A100 | 0.045

101 | 30-11-2023 | A101 | 0.035

101 | 30-11-2023 | A101 | 0.025

102 | 30-11-2023 | A123 | 0.055

102 | 30-11-2023 | A123 | 0.045

  • Output

Acctid | date | prodcode | rate

101 | 31-12-2023 | A100 | 0.045

101 | 30-11-2023 | A101 | 0.03

102 | 30-11-2023 | A123 | 0.05

Please help.

Select sum(rate) over(partition by acctid,date,prodcode order by date)/count(rate) over (partition by acctid,date,prodcode)

This gives

Output

rate

0.045

0.045

0.03

0.03

0.05

0.05

Community
  • 1
  • 1
user18288648
  • 1
  • 1
  • Looks like aggregation: `Select acctid,date,prodcode, avg(rate) from mytable group by 1,2,3` – dnoeth Jun 01 '23 at 06:19

0 Answers0