0

I know this is similar to this question: MySQL Query - Sum one related table - Count another related table but I cannot make it work for me.

I have 3 tables:

  • Event
  • Attendee
  • Gifts

For each Event we need to summarize the number of Attendees, and the total amount of Gifts. An Attendee may, or may not, receive a gift.

I can get it working so that if I ignore the gift totals, I get the right answer with this query:

SELECT event.name AS name, count( * ) AS num_attendee
FROM attendee
RIGHT JOIN event on event.id = attendee.event_id 
WHERE event.company_id =6
GROUP BY event.id

But I have no idea how to sum the gifts (i.e. Gift.Amount values) given to all Attendees for each event. When I try to simply join in the Gift table based upon event_id all the numbers go wacky.

Community
  • 1
  • 1
Garrett Donaldson
  • 3
  • 2
  • Do you have some sample inputs and outputs? – Explosion Pills Feb 22 '13 at 00:19
  • You are probably getting [cartesian product](http://en.wikipedia.org/wiki/Cartesian_product) because atendees and gifts both have more than one record per event_id. If this is the case use [derived table](http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values) to perform aggregation on child table(s) before you do a join to parent table. – Nikola Markovinović Feb 22 '13 at 00:46

2 Answers2

0

Are you doing the join correctly? You should have both the event and attendee in the gifts table:

from attendee right join
     event
     on event.id = attendee.event_id right join
     gifts
     on event.id = gifts.event_id and attendee.id = gifts.attendee_id
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

This is O'K in MS-SQL 2000:

SELECT event.name AS name, count( * ) num_attendee ,amount
FROM attendee
RIGHT JOIN event on event.id = attendee.event_id 
LEFT OUTER JOIN(
    SELECT event_id,amount=SUM(Amount)
    FROM Gifts
    GROUP BY event_id
) Gifts ON Gifts.event_id=event.id
WHERE event.company_id =6
GROUP BY event.name,amount
StackOverflow_kankyou
  • 16
  • 1