Postgres SQL GROUP BY without limiting rows

Question

I am running the following SQL query in Postgres:

SELECT a.id, a.name, array_to_string(array_agg(c.name), ',') AS tags_string, 
CASE d.is_reminder WHEN 't' then 'overdue' ELSE '' END AS overdue
FROM contacts AS a
LEFT OUTER JOIN taggings b ON b.contact_id=a.id
LEFT OUTER JOIN tags c ON b.tag_id=c.id
LEFT OUTER JOIN tasks d ON a.id=d.contact_id
GROUP BY a.id, d.is_reminder
ORDER BY a.id;

Which returns the following records from my db:

  id   |        name        |         tags_string          | overdue 
-------+--------------------+------------------------------+---------
 24471 | Austin Wang        |                              | 
 24472 | Chris Rothstein    | Seller                       | overdue
 24473 | Josh Hohman        | Seller                       | overdue
 24474 | Jay Pritchett      | Friends & Family             | 
 24475 | Claire Dunphy      | Past Client,Friends & Family | 
 24475 | Claire Dunphy      | Past Client,Friends & Family | overdue
 24476 | Haley Dunphy       | Buyer                        | overdue
 24477 | Cameron Tucker     | Friends & Family             | overdue
 24478 | Gloria Pritchett   | Friends & Family             | overdue
 24479 | Mitchell Pritchett | Buyer                        | overdue

I only want to return 1 row per id. In the results above, Claire Dunphy id:24475 appears twice: once with "overdue" and once without. If an id has an "overdue" then I want this record to appear.

If the contact does not have an overdue task, then I still want to display record. I just want to eliminate showing the non-overdue tasks (if any) for contacts that also have an overdue task.

In the results above, this means that I would show the second Claire Dunphy record, but not the first.

Any help is greatly appreciated!

peterm · Accepted Answer · 2013-06-04 01:36:40Z

2

Try

SELECT a.id, 
       a.name, 
       array_to_string(array_agg(c.name), ',') tags_string, 
       CASE WHEN strpos(array_to_string(array_agg(d.is_reminder), ','), 't') > 0 THEN 'overdue' ELSE '' END overdue
  FROM contacts a LEFT JOIN taggings b 
    ON b.contact_id = a.id LEFT JOIN tags c 
    ON b.tag_id = c.id LEFT JOIN tasks d 
    ON a.id = d.contact_id
 GROUP BY a.id

Here is SQLFiddle demo that shows the idea.

It obviously based on preaggregated sample data that you posted, but nevertheless shows how to get detect existence of overdue row for aggregated column.

edited Jun 4, 2013 at 1:36

answered Jun 4, 2013 at 1:25

peterm

93k16 gold badges157 silver badges162 bronze badges

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

2 Comments

Austin Wang Over a year ago

Thanks @peterm! this helps a lot. Although this works great, is there a better way to set conditions on the tasks in this case? What if we wanted tasks that were both is_reminder = true and completed_at IS NULL or something? Parsing a string from an array seems kind of like the long way (although I don't know of and haven't found what might be better). Something specific for an outer join, otherwise a WHERE clause would probably do the trick. Thanks!

peterm Over a year ago

@AustinWang You are more than welcome :) To be able to answer your question (which is much broader) intelligently there have to be available table's structures, relevant sample data and desired output(s) based on that sample data.

Hart CO · Accepted Answer · 2013-06-04 01:27:10Z

0

You need an aggregate, MAX() will pick something over nothing, so it works in this case:

SELECT a.id, a.name, array_to_string(array_agg(c.name), ',') AS tags_string, 
MAX(CASE d.is_reminder WHEN 't' then 'overdue' ELSE '' END) AS overdue
FROM contacts AS a
LEFT OUTER JOIN taggings b ON b.contact_id=a.id
LEFT OUTER JOIN tags c ON b.tag_id=c.id
LEFT OUTER JOIN tasks d ON a.id=d.contact_id
GROUP BY a.id, a.name, array_to_string(array_agg(c.name), ',')
ORDER BY a.id

answered Jun 4, 2013 at 1:27

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

1 Comment

jhohman Over a year ago

This didn't work for me. I still got the original 10 records. I tried changing: MAX(CASE d.is_reminder WHEN 't' then 'overdue' ELSE '' END) AS overdue ==> to MAX(CASE d.is_reminder WHEN 't' then 1 ELSE 0 END) AS overdue but it still didn't work for me.

Collectives™ on Stack Overflow

Postgres SQL GROUP BY without limiting rows

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related