CODEWITHSUNDEEP
sqlpostgresqlstring-aggregationstring-agg
Amir saleem
Amir saleem

Reputation: 1496

SQL: Use STRING_AGG with condition

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

Upvotes: -1

Views: 871

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

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.

Upvotes: 2

Related Questions