-1

I have the following PostgreSQL query: (This is a Kata at codewars, you can find it at the following link: https://www.codewars.com/kata/64956edc8673b3491ce5ad2c/train/sql )

SELECT * FROM (
  SELECT 
    s.id AS student_id,
    MIN(s.name) AS name,
    CASE 
      WHEN COUNT(c.course_name) = 0 THEN 'quit studying' 
      WHEN SUM(CAST(c.score < 60 AS INTEGER)) >=3
        THEN CONCAT('failed in ', STRING_AGG(CONCAT(c.course_name, '(', c.score, ')'), ', ' ORDER BY c.course_name)) 
      ELSE NULL
    END AS reason
  FROM students s
  FULL OUTER JOIN courses c
    ON c.student_id = s.id
  GROUP BY s.id
) df
WHERE reason IS NOT NULL
ORDER BY student_id

It works fine, but I need a little tweak, I am using STRING_AGG to join all rows in just one value, what I need to do is to include only rows with less then 60 score.

Query result right now:

enter image description here

Desired result:

enter image description here

Amir saleem
  • 1,404
  • 1
  • 8
  • 11
  • Filtering is done in the `WHERE` clause. If you want to aggregate only courses with a score above 60 you need to filter them in `WHERE`. – Panagiotis Kanavos Jul 11 '23 at 10:34
  • Thanks @PanagiotisKanavos for your reply. I can't do it, because I have other conditions to be checked, those conditions need all the data. – Amir saleem Jul 11 '23 at 10:36

1 Answers1

2

Use a filtered aggregate:

STRING_AGG(CONCAT(...), ', ' ORDER BY c.course_name) FILTER (WHERE c.score < 60)

That will only aggregate over the rows that match the condition, but there won't be any other effects on your query.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    In case someone has trouble finding the docs, see: [4.2.7. Aggregate Expressions](https://www.postgresql.org/docs/current/sql-expressions.html#:~:text=Section%C2%A08.16.5.-,4.2.7.%C2%A0Aggregate%20Expressions,-An%20aggregate%20expression) – Luuk Jul 11 '23 at 11:06