partition by returns duplicate rows

Question

Let's say I have a weather table:

MONTH, DAY, LOW
6, 1, 67.47084426063374
6, 2, 66.90195288599919
6, 3, 62.16084275276963

I want to select the avg low temperature for the month and am trying this:

SELECT t.* FROM (
    select p.month, avg(p.low) over (partition by p.month) avg_low
    from table p
) t;

The averaging is working but I'm getting 3 duplicate records back. How can I just return a single record?

(Also, when I try to round these round(avg(p.low),2), I'm told "FROM keyword not found where expected")

You have placed a comma after avg_low please remove it. – Atif Aug 12 '20 at 18:50 — Atif, Aug 12 '20 at 18:50

score 3 · Accepted Answer · answered Aug 12 '20 at 18:51

3

I think you just want aggregation:

select p.month, avg(p.low) as avg_low
from table p
group by p.month

answered Aug 12 '20 at 18:51

Gordon Linoff

score 1 · Answer 2 · answered Aug 12 '20 at 18:52

1

SELECT MONTH,ROUND(AVG(LOW),2) avg_low FROM  table  GROUP BY MONTH

That's it :)

answered Aug 12 '20 at 18:52

2 Answers2