Run WHERE over an Average aggregation result - postgresql

Question

I have the following query that gets the papers grade listing.

SELECT DISTINCT papers.paper_id, papers.paper_title, AVG(paper_judge_participations.paper_judge_participation_score) AS final_grade,
(SELECT array_agg(paper_author_name) FROM paper_authors as authors WHERE authors.paper_id=papers.paper_id )::varchar as paper_author_name
FROM papers 
FULL JOIN paper_categories ON paper_categories.paper_category_id=papers.paper_category_id
LEFT JOIN paper_judge_participations ON papers.paper_id = paper_judge_participations.paper_id 
WHERE ((papers.paper_note IS NULL AND final_grade >= 7) OR (papers.paper_note IS NOT NULL AND papers.paper_note >= 7)) AND papers.paper_category_id = 1
GROUP BY papers.paper_id ORDER BY final_grade, papers.paper_note;

I want to see if the final_grade variable from the the averae result is more than 7 but I get the error:

ERROR:  column "final_grade" does not exist
LINE 6: WHERE ((papers.paper_note IS NULL AND final_grade >= 7) OR (...

How can I use the average result on my WHERE condition?

You need to put the avg condition in a "HAVING" clause, which comes after the GROUP BY — mlinth
– mlinth, Commented Aug 26, 2015 at 18:38
I'm new to SQL. Can you please answer the question with the script, @mlinth ? — Joabe da Luz
– Joabe da Luz, Commented Aug 26, 2015 at 18:40

Gordon Linoff · Accepted Answer · 2015-08-26 18:45:34Z

2

The distinct is unnecessary in the SELECT. Also you need to move the logic to the HAVING clause:

SELECT p.paper_id, p.paper_title,
       AVG(pjp.paper_judge_participation_score) AS final_grade,
       (SELECT string_agg(paper_author_name)
        FROM paper_authors pa
        WHERE pa.paper_id = p.paper_id
       ) as paper_author_name
FROM papers p LEFT JOIN
     paper_categories pc
     ON pc.paper_category_id = p.paper_category_id LEFT JOIN 
     paper_judge_participations pjp
     ON p.paper_id = pjp.paper_id 
HAVING p.paper_category_id = 1
GROUP BY p.paper_id
HAVING (p.paper_note IS NULL AND final_grade >= 7) OR
       (p.paper_note IS NOT NULL AND p.paper_note >= 7) AND 
ORDER BY final_grade, p.paper_note;

Comments:

The FULL OUTER JOIN is being turned into a LEFT OUTER JOIN by the WHERE clause. So, you might as well be explicit.
Instead of doing array_agg() and converting the results to a string, how about just using string_agg()?
Table aliases make the query easier to write and read.
And of course, the conditions on the final grade have been moved to the HAVING clause. The condition on the group stays in the WHERE.

Personally, I find it strange that you are using a correlated subquery for one aggregation and explicit aggregation for the other. I suppose that is a matter of preference. Under the circumstance, you might consider using a correlated subquery for both.

answered Aug 26, 2015 at 18:45

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joabe da Luz Over a year ago

The system I'm working uses a old version of postgresql and some functions like string_agg() don't exist.

Joabe da Luz Over a year ago

I ended using the having, and it's working now. Thanks.

Collectives™ on Stack Overflow

Run WHERE over an Average aggregation result - postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related