StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you use a master table for pledges, you can get all the info out in one go like this:</p> <pre><code>SELECT People.PersonName, pledgetotals.PledgeTotal, pledgecounts.PledgesCorrect FROM People LEFT OUTER JOIN (SELECT PeoplePledges.peopleID, SUM(PeoplePledges.pledgeAmount) AS PledgeTotal FROM PeoplePledges GROUP BY PeoplePledges.peopleID) pledgetotals ON People.peopleID = pledgetotals.peopleID LEFT OUTER JOIN (SELECT PeoplePledges.peopleID, COUNT(DISTINCT PeoplePledges.pledgeID) AS PledgesCorrect FROM PeoplePledges JOIN Pledge ON PeoplePledges.pledgeID = Pledge.pledgeID WHERE Pledge.correct = 1 GROUP BY PeoplePledges.peopleID) pledgecounts ON People.peopleID = pledgecounts.peopleID </code></pre> <p>Hope it isn't too unclear; each subquery is needed to do the aggregation (summing pledge amounts and counting correct pledges respectively); and if you arrange the query this way round with the left outer joins, you can list all the people involved whether they've actually got any pledges going or not.</p> <p><strong>Edit:</strong> This is what I mean by the 'master table' of pledges:</p> <pre><code>CREATE TABLE Pledge (INT pledgeID INT PRIMARY KEY, correct BOOLEAN NOT NULL); INSERT INTO Pledge (pledgeID, correct) VALUES (1, 1); INSERT INTO Pledge (pledgeID, correct) VALUES (2, 0); INSERT INTO Pledge (pledgeID, correct) VALUES (3, 1); INSERT INTO Pledge (pledgeID, correct) VALUES (4, 0); INSERT INTO Pledge (pledgeID, correct) VALUES (5, 1); INSERT INTO Pledge (pledgeID, correct) VALUES (6, 0); </code></pre> <p><strong>Edit:</strong> If you can't add a master table of pledges, then you have to use 'magic numbers' in your code but the structure is pretty similar:</p> <pre><code>SELECT People.PersonName, pledgetotals.PledgeTotal, pledgecounts.PledgesCorrect FROM People JOIN (SELECT PeoplePledges.peopleID, SUM(PeoplePledges.pledgeAmount) AS PledgeTotal FROM PeoplePledges GROUP BY PeoplePledges.peopleID) pledgetotals ON People.peopleID = pledgetotals.peopleID JOIN (SELECT PeoplePledges.peopleID, COUNT(DISTINCT PeoplePledges.pledgeID) AS PledgesCorrect FROM PeoplePledges WHERE PeoplePledges.pledgeID IN (1,3,5) GROUP BY PeoplePledges.peopleID) pledgecounts ON People.peopleID = pledgecounts.peopleID </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload