Clearly summarize a column on a joined table?

Question

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

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

Explanation:

• The ticket belongs to the participant
• The visitor has an income.
• I need to group tickets into sections and get a total income.
• This double count visits the participants because 2 tickets can belong to the same member, thus double counting. I would like to receive the amount of income, but only counting visitors once.

In my sqlfiddle example, I would like:

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

I would like to solve this problem without using subqueries. I need a scalable solution that will allow me to do this for multiple columns in different joins in the same query. So, that allows me to do this, I am open to suggestions.

Thank you for your help.

Answer 1

Here is the 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

Answer 2

Again, without a subquery :

The key element is adding PARTITION BY to the window functions:

 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 are 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, since you want this time the results to be partitioned.

Uncomment the second line if you want a ticket score.

Otherwise, it works similarly to my answer to your previous question . Ie, the rest of the explanation applies.

Answer 3

You can do it:

 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;