Get the distinct sum of a joined table column

by

To get the result without subquery, you have to resort to advanced window function trickery:

SELECT sum(count(*))       OVER () AS tickets_count
     , sum(min(a.revenue)) OVER () AS atendees_revenue
FROM   tickets   t
JOIN   attendees a ON a.id = t.attendee_id
GROUP  BY t.attendee_id
LIMIT  1;

sqlfiddle

How does it work?

The key to understanding this is the sequence of events in the query:

aggregate functions -> window functions -> DISTINCT -> LIMIT

More details:

Step by step:

  1. I GROUP BY t.attendee_id – which you would normally do in a subquery.

  2. Then I sum over the counts to get the total count of tickets. Not very efficient, but forced by your requirement. The aggregate function count(*) is wrapped in the window function sum( ... ) OVER () to arrive at the not-so-common expression: sum(count(*)) OVER ().

    And sum the minimum revenue per attendee to get the sum without duplicates.

    You could also use max() or avg() instead of min() to the same effect as revenue is guaranteed to be the same for every row per attendee.

    This could be simpler if DISTINCT was allowed in window functions, but PostgreSQL has not (yet) implemented this feature. Per documentation:

    Aggregate window functions, unlike normal aggregate functions, do not
    allow DISTINCT or ORDER BY to be used within the function argument list.

  3. Final step is to get a single row. This could be done with DISTINCT (SQL standard) since all rows are the same. LIMIT 1 will be faster, though. Or the SQL-standard form FETCH FIRST 1 ROWS ONLY.

More Related Contents:

  1. PostgreSQL: running count of rows for a query ‘by minute’
  2. Postgres window function and group by exception
  3. PostgreSQL unnest() with element number
  4. Aggregate columns with additional (distinct) filters
  5. Dynamic alternative to pivot with CASE and GROUP BY
  6. Two SQL LEFT JOINS produce incorrect result
  7. How to delete duplicate entries?
  8. Create a pivot table with PostgreSQL
  9. Calculating Cumulative Sum in PostgreSQL
  10. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  11. Return multiple columns of the same row as JSON array of objects
  12. How to include “zero” / “0” results in COUNT aggregate?
  13. GROUP BY and aggregate sequential numeric values
  14. Delete duplicate rows (don’t delete all duplicate)
  15. GROUP BY + CASE statement
  16. Best way to count rows by arbitrary time intervals
  17. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  18. Selecting data into a Postgres array
  19. Compute percents from SUM() in the same SELECT sql query
  20. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  21. Window Functions or Common Table Expressions: count previous rows within range
  22. Aggregate a single column in query with many columns
  23. Multiple array_agg() calls in a single query
  24. SQL: difference between PARTITION BY and GROUP BY
  25. Count cumulative total in Postgresql
  26. Referencing current row in FILTER clause of window function
  27. How to group timestamps into islands (based on arbitrary gap)?
  28. How to use a ring data structure in window functions
  29. Truncate timestamp to arbitrary intervals
  30. Conditional lead/lag function PostgreSQL?

Leave a Comment