CODEWITHSUNDEEP
sqlpostgresqlgroup-bycase-when
nitarshs
nitarshs

Reputation: 193

Trying to GROUP BY with CASE WHEN THEN - postgresql

I have three tables PATH, ELEMENTS and ELEMENT_DETAILS with the following structure:

PATH:

ID
1
2
3

ELEMENTS:

ID | PATH_ID | DIRECTION | ELEMENT_DETAILS_ID
1    1         'left'      1
2    1         'right'     2
3    2         'left'      3
4    2         'right'     2

ELEMENT_DETAILS:

ID | NAME
1    'Henry'
2    'Mark'
3    'John'

I would like the result to be like so:

ID  | left    |   right
1    'Henry'      'Mark'
2    'John'       'Mark'

This is the SQL I have come up with so far:

      SELECT path.id, 
             CASE WHEN elements.direction='left' THEN element_details.name
             ELSE NULL END
            as left,
             CASE WHEN elements.direction='right' THEN element_details.name
             ELSE NULL END
            as right,
      FROM elements
      INNER JOIN path on elements.path_id = path.id
      LEFT JOIN element_details on elements.element_details_id = element_details.id
      GROUP BY path.id
      ORDER BY path.id

However, this does not work since postgres gives me an error saying elements.direction should be in group by. And including elements.direction in group_by does not give me aggregation at path.id level.

Stuck on this. Can someone please help. I am using Postgres version 9.5

Upvotes: 0

Views: 661

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You need aggregation. Here is one method:

SELECT p.id, 
       MAX(CASE WHEN e.direction = 'left' THEN ed.name
           END) as left,
       MAX(CASE WHEN e.direction = 'right' THEN ed.name
           END) as right
FROM elements e INNER JOIN
     path p
     ON e.path_id = p.id LEFT JOIN
     element_details ed
     ON e.element_details_id = ed.id
GROUP BY p.id
ORDER BY p.id

Upvotes: 2

Related Questions