I have two tables; one shows all the event and the users who were invited to the event and their RSVP status. You can see the code I created at SQLFiddle
CREATE TABLE EventInvitation(
InvitedUserId INT,
EventId INT,
IsAccepted BIT
)
INSERT INTO EventInvitation VALUES (1,1,1)
INSERT INTO EventInvitation VALUES (2,1,1)
INSERT INTO EventInvitation VALUES (1,2,1)
INSERT INTO EventInvitation VALUES (1,3,0)
INSERT INTO EventInvitation VALUES (2,3,1)
INSERT INTO EventInvitation VALUES (3,3,0)
CREATE TABLE EventRSVPComment(
EventId INT,
Comment VARCHAR(50)
)
INSERT INTO EventRSVPComment VALUES (1,'comment1_1')
INSERT INTO EventRSVPComment VALUES (1,'comment1_2')
INSERT INTO EventRSVPComment VALUES (1,'comment1_3')
INSERT INTO EventRSVPComment VALUES (2,'comment2_1')
INSERT INTO EventRSVPComment VALUES (2,'comment2_2')
INSERT INTO EventRSVPComment VALUES (2,'comment2_3')
INSERT INTO EventRSVPComment VALUES (3,'comment3_1')
INSERT INTO EventRSVPComment VALUES (3,'comment3_2')
The next table is the event comments:
I need to write a query to show each event with its total invites, total accepted and total comments like the table below:
So far I was able to display the invite & accepted using the query below, I could not get the total comment to show up correctly yet.
SELECT
EI.EventId,
COUNT(*) AS Invited,
SUM(CASE WHEN EI.IsAccepted = 1 THEN 1 ELSE 0 END) AS Accepted
FROM EventInvitation EI
GROUP BY EI.EventId
I have tried to fiddle with left join, inner join etc. without luck, I hope someone can point me in the right direction.
>Solution :
In this case the easiest is to just use a correlated subquery
SELECT
EI.EventId,
COUNT(*) AS Invited,
SUM(CASE WHEN EI.IsAccepted = 1 THEN 1 ELSE 0 END) AS Accepted,
(SELECT COUNT(*) FROM EventRSVPComment c WHERE c.EventId = EI.EventId) AS TotalComments
FROM EventInvitation EI
GROUP BY EI.EventId