CODEWITHSUNDEEP
sqlpostgresqlaverageaggregation
Joabe da Luz
Joabe da Luz

Reputation: 1020

Run WHERE over an Average aggregation result - postgresql

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?

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

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.

Upvotes: 2

Related Questions