I need make a subquery

Question

I have two tables: invitations and events.

I need get the name of the event, the total of guest per event and the total of presents when its true per event in the same query!

Something like join both tables (left join) selecting events.name, count(invitations.guest), count(invitations.presents when = true) and group it by events...

Looking the tables data...

I think it could work but I don't get what I want...

SELECT e.name, count(in.guest) as Guests, (SELECT count(presents) 
FROM watermelon.invitations WHERE presents = true) as Presents 
FROM watermelon.events e LEFT JOIN watermelon.invitations in ON e.id = in.event 
GROUP BY in.event;

Then I get:

http://dl.dropbox.com/u/360112/Duda/resultado.jpg

Some suggestion? Please I need it, and I'm tired of trying and getting wrong results... Thanks in advance!

Answer 1

Sounds like you want something like this using COUNT, GROUP BY and CASE:

SELECT E.Id, 
    E.Name, 
    COUNT(I.Id) TotalGuests,
    COUNT(CASE WHEN I.Presents = 'TRUE' THEN 1 END) TotalPresents
FROM Events E
    LEFT JOIN Invitations I ON E.Id = I.Event
GROUP BY E.Id, E.Name

I'm not positive about your data type of Presents, but this should be close. Also using a LEFT JOIN to return all events.