Multiple COUNT(*) with join

Question

I have to COUNT some rows from multiple tables. Before I can do multiple COUNT I will have to subselect. The problem here is that I need to JOIN some values in order to get the right result.

SELECT
    sponsor.Name As SponsorName,
    COUNT(participants.[Table]) AS ParticipantCount,
    (   SELECT 
            COUNT(guestcards.[Table])
        FROM
            guestcards
        WHERE
            guestcards.EventID = @EventID
            AND
            guestcards.[Table] = @Table
            AND
            guestcards.SponsorID = participants.SponsorID
            -- Here lies the problem. 
            -- I will need to check up on another value to ensure I get the right rows, but  participants.SponsorID is not here because of no join :-(

    )  AS GuestParticipantCount
FROM
    participants
    LEFT JOIN
        sponsor
    ON
        sponsor.ID = participants.SponsorID
WHERE
    participants.EventID = @EventID
    AND
    participants.[Table] = @Table
GROUP BY
    sponsor.Name

Guestcards table holds: sponsorid, eventid, tablename

Participantstable holds: sponsorid, eventid, tablename

Sponsor table holds: id, name

I need to count how many "Participants" there are and how many "Guestcards" that in a particulary event. These participants have a table (where they should sit) and so does the guestcards. I need to check up on if its the same "table" where they sit.

So I need to count how many are sitting at table "A1" or table "A2" etc.

The result I am after is like:

"Sponsor Name has 5 participants and 3 guestcards. They sit on A1"

I hope I made my self clear

Answer 1

Here's exact equivalent of you query (grouping on sponsor.Name):

SELECT  sponsor.name,
        COALESCE(SUM(participantCount), 0),
        COALESCE(SUM(guestcardsCount), 0)
FROM    (
        SELECT  sponsorId, COUNT(*) AS participantCount
        FROM    participants
        WHERE   eventId = @eventId
                AND [table] = @table
        GROUP BY
                sponsorId
        ) p
FULL JOIN
        (
        SELECT  sponsorId, COUNT(*) AS guestcardsCount
        FROM    guestdcards
        WHERE   eventId = @eventId
                AND [table] = @table
        GROUP BY
                sponsorId
        ) g
ON      g.sponsorId = p.sponsorId
FULL JOIN
        sponsor s
ON      s.id = COALESCE(p.sponsorId, g.sponsorId)
GROUP BY
        s.sponsorName

However, I believe you want something more simple:

SELECT  sponsorName, participantCount, guestcardsCount
FROM    sponsor s
CROSS APLLY
        (
        SELECT  COUNT(*) AS participantCount
        FROM    participants
        WHERE   sponsorId = s.id
                AND eventId = @eventId
                AND [table] = @table
        ) p
CROSS APLLY
        (
        SELECT  COUNT(*) AS guestcardsCount
        FROM    guestdcards
        WHERE   sponsorId = s.id
                AND eventId = @eventId
                AND [table] = @table
        ) g

Update:

SELECT  sponsor.name,
        COALESCE(participantCount, 0),
        COALESCE(guestcardsCount, 0)
FROM    (
        SELECT  sponsorId, COUNT(*) AS participantCount
        FROM    participants
        WHERE   eventId = @eventId
                AND [table] = @table
        GROUP BY
                sponsorId
        ) p
FULL JOIN
        (
        SELECT  sponsorId, COUNT(*) AS guestcardsCount
        FROM    guestdcards
        WHERE   eventId = @eventId
                AND [table] = @table
        GROUP BY
                sponsorId
        ) g
ON      g.sponsorId = p.sponsorId
JOIN    sponsor s
ON      s.id = COALESCE(p.sponsorId, g.sponsorId)