CODEWITHSUNDEEP
sqlpostgresqldistinctaggregate-functions
Binary Logic
Binary Logic

Reputation: 1547

Distinctly sum a column on a joined table?

This is a simple problem, and I'm not sure if its possible here. Here's the problem:

=> http://sqlfiddle.com/#!12/584f1/7

Explanation:

In my sqlfiddle example, I'd like to see:

section | total_revenue
------------------------
A       | 40            <= 40 is correct, but I'm getting 50...
B       | null
C       | 40

I'd like to solve this without the use of sub queries. I need a scalable solution that will allow me to do this for multiple columns on different joins in a single query. So whatever allows me to accomplish this, I'm open to suggestions.

Thanks for your help.

Upvotes: 4

Views: 255

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656251

Again, without subquery:

Key element is to add PARTITION BY to the window function(s):

SELECT DISTINCT
       t.section
--    ,sum(count(*))       OVER (PARTITION BY t.section) AS tickets_count
      ,sum(min(a.revenue)) OVER (PARTITION BY t.section) AS atendees_revenue
FROM   tickets t
LEFT   JOIN attendees a ON a.id = t.attendee_id
GROUP  BY t.attendee_id, t.section
ORDER  BY t.section;

-> sqlfiddle

Here, you GROUP BY t.attendee_id, t.section, before you run the result through the window function. And use PARTITION BY t.section in the window function as you want results partitioned by section this time.

Uncomment the second line if you want to get a count of tickets, too.

Otherwise, it works similar to my answer to your previous question. I.e., the rest of the explanation applies.

Upvotes: 1

Taryn
Taryn

Reputation: 247620

Here is a version using row_number():

select section,
  sum(revenue) Total
from 
(
  select t.section, a.revenue,
    row_number() over(partition by a.id, t.section order by a.id) rn
  from tickets t
  left join attendees a
    on t.attendee_id = a.id
) src
where rn = 1
group by section
order by section;

See SQL Fiddle with Demo

Upvotes: 1

rs.
rs.

Reputation: 27427

You can do this:

select t.section, sum(d.revenue)
from 
(
  SELECT DISTINCT section, attendee_id FROM tickets
) t
left join attendees d on t.attendee_id = d.id
group by t.section
order by t.section;

Upvotes: 0

Related Questions