Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

This is a pretty basic problem and for whatever reason I can't find a reasonable solution. I'll do my best to explain.

Say you have an event ticket (section, row, seat #). Each ticket belongs to an attendee. Multiple tickets can belong to the same attendee. Each attendee has a worth (ex: Attendee #1 is worth $10,000). That said, here's what I want to do:

1. Group the tickets by their section
2. Get number of tickets (count)
3. Get total worth of the attendees in that section

Here's where I'm having problems: If Attendee #1 is worth $10,000 and is using 4 tickets, sum(attendees.worth) is returning $40,000. Which is not accurate. The worth should be $10,000. Yet when I make the result distinct on the attendee, the count is not accurate. In an ideal world it'd be nice to do something like

select 
    tickets.section, 
    count(tickets.*) as count, 
    sum(DISTINCT ON (attendees.id) attendees.worth) as total_worth 
from 
    tickets 
    INNER JOIN 
    attendees ON attendees.id = tickets.attendee_id 
GROUP BY tickets.section

Obviously this query doesn't work. How can I accomplish this same thing in a single query? OR is it even possible? I'd prefer to stay away from sub queries too because this is part of a much larger solution where I would need to do this across multiple tables.

Also, the worth should follow the ticket divided evenly. Ex: $10,000 / 4. Each ticket has an attendee worth of $5,000. So if the tickets are in different sections, they take their prorated worth with them.

Thanks for your help.

share|improve this question
 
I know it's been a while, but I'm having the exact same type of problem and I'm wondering if you were able to solve it. –  denaje Apr 18 at 11:18
add comment

1 Answer

up vote 1 down vote

You need to aggregate the tickets before the attendees:

select ta.section, sum(ta.numtickets) as count, sum(a.worth) as total_worth
from (select attendee_id, section, count(*) as numtickets
      from tickets
      group by attendee_id, section
     ) ta INNER JOIN
     attendees a
     ON a.id = ta.attendee_id
GROUP BY ta.section

You still have a problem of a single attendee having seats in multiple sections. However, you do not specify how to solve that (apportion the worth? randomly choose one section? attribute it to all sections? canonically choose a section?)

share|improve this answer
 
Thanks for this, but isn't this still counting the worth multiple times? The worth is determined by the number of tickets. Ex: Attendee #1 has a worth of $10,000 and is using 4 tickets. Each ticket contributes $5,000 to the worth. So if each ticket was in a different section, it would add $5,000 to its respective section. Does that make sense? –  Binary Logic Oct 8 '12 at 17:52
 
Doesn't this generate an error? The derived table ("ta") does not group properly. I'm pretty sure PostgreSQL will reject that. –  a_horse_with_no_name Oct 8 '12 at 17:59
 
@a_horse_with_no_name . . . Yes, I fixed the code to match my intent. –  Gordon Linoff Oct 8 '12 at 18:01
1  
@user851181: seems to counting correctly: sqlfiddle.com/#!12/ca969/2 –  a_horse_with_no_name Oct 8 '12 at 18:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.