Aggregate a single column in query with many columns

by

Simple query

This can be much simpler with PostgreSQL 9.1 or later. As explained in this closely related answer:

It is enough to GROUP BY the primary key of a table. Since:

foo1 is a primary key

.. you can simplify your example to:

SELECT foo1, foo2, foo3, foo4, foo5, foo6, string_agg(aggregated_field, ', ')
FROM   tbl1
GROUP  BY 1
ORDER  BY foo7, foo8;  -- have to be spelled out, since not in select list!

Query with multiple tables

However, since you have:

many more fields and LEFT JOINs, the important part is that all these fields have 1 to 1 or 1 to 0 relationship except one field that is 1 to n which I want to aggregate

.. it should be faster and simpler to aggregate first, join later:

SELECT t1.foo1, t1.foo2, ...
     , t2.bar1, t2.bar2, ...
     , a.aggregated_col 
FROM   tbl1 t1
LEFT   JOIN tbl2 t2 ON ...
...
LEFT   JOIN (
   SELECT some_id, string_agg(agg_col, ', ') AS aggregated_col
   FROM   agg_tbl a ON ...
   GROUP  BY some_id
   ) a ON a.some_id = ?.some_id
ORDER  BY ...

This way the big portion of your query does not need aggregation at all.

I recently provided a test case in an SQL Fiddle to prove the point in this related answer:

Since you are referring to this related answer: No, DISTINCT is not going to help at all in this case.

More Related Contents:

  1. Aggregate columns with additional (distinct) filters
  2. PostgreSQL: running count of rows for a query ‘by minute’
  3. Two SQL LEFT JOINS produce incorrect result
  4. Create a pivot table with PostgreSQL
  5. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  6. Return multiple columns of the same row as JSON array of objects
  7. How to include “zero” / “0” results in COUNT aggregate?
  8. GROUP BY + CASE statement
  9. Best way to count rows by arbitrary time intervals
  10. Selecting data into a Postgres array
  11. Compute percents from SUM() in the same SELECT sql query
  12. Get the distinct sum of a joined table column
  13. Multiple array_agg() calls in a single query
  14. Postgres window function and group by exception
  15. Count cumulative total in Postgresql
  16. Truncate timestamp to arbitrary intervals
  17. Computed / calculated / virtual / derived columns in PostgreSQL
  18. Insert data in 3 tables at a time using Postgres
  19. sql ORDER BY multiple values in specific order?
  20. PostgreSQL: Which Datatype should be used for Currency?
  21. Finding similar strings with PostgreSQL quickly
  22. Window Functions or Common Table Expressions: count previous rows within range
  23. Alphanumeric sorting with PostgreSQL
  24. Transposing an sql result so that one column goes onto multiple columns
  25. How to convert PostgreSQL 9.4’s jsonb type to float
  26. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  27. How to get a list column names and datatypes of a table in PostgreSQL?
  28. Referencing current row in FILTER clause of window function
  29. Postgresql query between date ranges
  30. How to use SQL LIKE condition with multiple values in PostgreSQL?

Leave a Comment