Multiple criteria with count of matches

Question

I have 2 tables, a table of people with peopleID and name, and a table of pledges they have made which has a pledgeID (1 to 6) a personID to say who it came from, and an amount field.

We need to have a query that gives a distinct list of people, how much they have pledged in total, and the number of pledges they have got correct (pledgeIDs 1,3 and 5 would be regarded as correct)

So we would need to know for example that John Smith has pledged £500 in total and he matched 2 pledges (because he had made pledges on 1,3 and 6 for example) and Sally James has pledged £2000 and has pledged on ids 1,3 and 5 and has therefore matched 3

I hope this is clear. I would really appreciate some help with this one.

Many thanks

Dave

Answer 1

Sounds like something like this would work:

SELECT PersonID,
    SUM(Amount) AS TotalPledged,
    SUM(CASE WHEN PledgeID IN (1,3,5) THEN 1 ELSE 0 END) AS CorrectPledges
FROM PersonPledges
GROUP BY PersonID

Answer 2

The easiest way would be to:

1- Get all the people from your people's table, e.g something like this:

SELECT peopleId, name FROM people

2- Loop through each row and get the pledges for each person. In PHP you would do something like this:

foreach ($people as $k=>$person)
{
  $sql = "SELECT SUM(amount) as total FROM pledges
            WHERE peopleId = '$person[peopleId]'";
  $result = mysql_query($sql);
  if (mysql_num_rows($result) <= 0)
     $total = '0';
  else
  {
     $row = mysql_fetch_assoc($result);
     $total = $row['total'];
  }
  $people[$k]['total'] = $total;
}

Answer 3

If you use a master table for pledges, you can get all the info out in one go like this:

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

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.

Edit: This is what I mean by the 'master table' of pledges:

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);

Edit: 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:

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