Meerdere COUNT (*) met join

Ik moet enkele rijen COUNTEN uit meerdere tabellen. Voordat ik meerdere COUNT I kan doen, moet ik subselecteren. Het probleem hier is dat ik me bij een aantal waarden moet aansluiten om het juiste resultaat te krijgen.

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

De tabel met gastenkaarten houdt in: sponsorid, eventid, tablename

Deelnemerstabiel: sponsorid, eventid, tablename

Sponsortabel houdt in: id, naam

Ik moet tellen hoeveel "deelnemers" er zijn en hoeveel "gastenkaarten" in een bepaalde gebeurtenis. Deze deelnemers hebben een tafel (waar ze moeten zitten) en ook de gastenkaarten. Ik moet controleren of het dezelfde 'tafel' is waar ze zitten.

Dus ik moet tellen hoeveel aan tafel "A1" of tafel "A2" etc. zitten

Het resultaat waar ik achter sta is als:

"Sponsornaam heeft 5 deelnemers en 3 gastenkaarten. Ze zitten op A1"

Ik hoop dat ik mezelf duidelijk heb gemaakt

1
Moet u echt groeperen op sponsor.name en niet sponsor.id ? Ook is hier LEFT JOIN opzettelijk?
toegevoegd de auteur Quassnoi, de bron
Is het mogelijk dat deelnemers.sponsorId of gastcars.sponsorId niet overeenkomt met een item in sponsor ?
toegevoegd de auteur Quassnoi, de bron
En als er twee sponsors zijn met dezelfde naam maar met een andere id, wil je ze dan apart tonen?
toegevoegd de auteur Quassnoi, de bron
Wat probeer je precies te krijgen?
toegevoegd de auteur Barry Jordan, de bron
Zie bewerken. Ik hoop dat het veel beter is, moeilijk uit te leggen
toegevoegd de auteur janhartmann, de bron
Hoe is de tabel met gastenkaarten gerelateerd aan de tabellen voor deelnemers en sponsors?
toegevoegd de auteur Maess, de bron

1 antwoord

Hier is het exacte equivalent van uw zoekopdracht (groeperen op 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

Ik geloof echter dat je iets eenvoudiger wilt:

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)
2
toegevoegd
@meep: zie de berichtupdate
toegevoegd de auteur Quassnoi, de bron
Je tweede antwoord werkt prima, maar het levert sponsors op die niet aan het evenement hebben deelgenomen. (met 0 in zowel deelnemerCount en guestcardsCount) Is daar een manier omheen?
toegevoegd de auteur janhartmann, de bron
Het lijkt erop dat dat de slag heeft gedaan. Veel geavanceerder dan ik had verwacht. Dank je. Bespaarde me uren werk!
toegevoegd de auteur janhartmann, de bron