CODEWITHSUNDEEP
sqlwindow-functions
Jelly
Jelly

Reputation: 1298

SQL avg function in partition by

I have a task to count average-value of 3 columns in a section of 3 criteria.

This is a source-table:

enter image description here

I need to select average values of primary needs, working or leisure, grouped by the different ages of life (young, active or elder), sex and working status.

For this purpose I apply the following query with window-function:

SELECT
    working, sex, age, 
    ROUND(AVG(primaryNeeds) OVER(w),1) primaryNeeds,
    ROUND(avg(work) OVER(w),1) work,
    ROUND(avg(other)OVER(w),1) other
FROM 
    demography
WINDOW w AS (PARTITION BY 'age', 'working', 'sex' ORDER BY 'age', 'working', 'sex')

But in the resulting selection average values counted without grouping by 'age', 'working', 'sex' - columns.

enter image description here

What am I doing wrong?

Upvotes: 0

Views: 9978

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If you want to preserve the original rows and append the averages, then you are on the right track. However, I think the issue is the quotes in the window clause. And the order by is superfluous:

SELECT d.*, 
       ROUND(AVG(primaryNeeds) OVER w, 1) as primaryNeeds,
       ROUND(avg(work) OVER w, 1) as work,
       ROUND(avg(other) OVER w, 1) as other
FROM demography d
WINDOW w AS (PARTITION BY age, working, sex)

Upvotes: 0

Steven
Steven

Reputation: 15258

If you want to group, simply use a group by:

SELECT working,
       sex,
       age,
       ROUND(AVG(primaryNeeds), 1) primaryNeeds,
       ROUND(avg(WORK), 1) WORK,
       ROUND(avg(other), 1) other
FROM demography 
group by age, working, sex

Upvotes: 3

Related Questions