1

I have one table of peoples names and I have another table of pledges these people have made.

They might pledge money against item 1,2,3 or 4.

Each time a pledge is made an entry is made in the pledge table with the id of the person and the pledge number they made.

I would like a query that gets a count of distinct people that made pledges for both 1 and 2 for example.

How can this be achieved?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Dave
  • 153
  • 9

1 Answers1

1

You could try this using the EXISTS

SELECT     COUNT(DISTINCT PersonID) DistinctCountPersons
FROM       pledges p
WHERE     EXISTS(SELECT * FROM pledges WHERE PersonID = p.PersonID AND ItemID = 1)
AND     EXISTS(SELECT * FROM pledges WHERE PersonID = p.PersonID AND ItemID = 2)

EDIT

Regarding the second part of the query, you can try

SELECT  p.PersonID,
        pp.PersonName,
        SUM(p.Pledge) TotalPledged
FROM    pledges p INNER JOIN
        people pp ON p.PersonID = pp.PersonID
WHERE   EXISTS(SELECT * FROM pledges WHERE PersonID = p.PersonID AND ItemID = 1)
AND     EXISTS(SELECT * FROM pledges WHERE PersonID = p.PersonID AND ItemID = 2)
GROUP BY p.PersonID,pp.PersonName
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • That's fantastic, and quick!! Seems to do exactly what I need, although I'm not entirely sure I understand how it is working. My sql knowledge is a little basic! Thanks anyway – Dave Jan 13 '10 at 15:18
  • Can I pick your brain a little further please. We have this part working now which is a big help, but now the query needs to go a bit further. There is another table that contains the person's name linked by the personID column. We would now like to get a list of names of people that match the two criteria and also how much they had pledged as a total (this is contained in the pledges table and they may make multiple pledges for the same item). Many thanks Dave – Dave Jan 13 '10 at 16:41
  • I have updated the answer, have a look, and let me know if this helps, or if yuo require any further help. astander. – Adriaan Stander Jan 13 '10 at 16:43
  • Only a pleasure. Glad I could help X-) – Adriaan Stander Jan 13 '10 at 17:30