On Wed, 31 Oct 2007 17:47:54 +0100, <jimnl69@hotmail.com> wrote:
> On Oct 31, 12:23 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Wed, 31 Oct 2007 16:55:21 +0100, <jimn...@hotmail.com> wrote:
>> > I have a table that I get names and counts from but do it in 2
>> > seperate queries. I have to imagine it's possible in 1 cool query.
>>
>> > Table:
>>
>> > Name Value Date
>> > John A 2007-10-30
>> > John B 2007-10-30
>> > John C 2007-10-30
>> > John D 2007-10-30
>> > John E 2007-10-30
>> > Mark A 2007-10-31
>> > Lucy C 2007-10-31
>> > Lucy D 2007-10-31
>> > Mark D 2007-11-01
>> > Hank A 2007-11-01
>> > Hank D 2007-11-02
>>
>> > What I want from this is:
>> > John 3
>> > Mark 1
>> > Lucy 1
>>
>> > Basically, I want a count per distinct Name of each Value that is
>> > "given" to someone else.
>>
>> I can't get the logic. Could you explain some more?
>>
>> If I hazard a guess, I'd say you actually want this:
>>
>> SELECT
>> t.Name,
>> COUNT(DISTINCT t.Value)
>> FROM table t
>> JOIN table j
>> ON j.Value = t.Value # something belongs are has belonged to
>> this person
>> AND j.Name != t.Name # but also belongs or has belonged to
>> someone else
>> AND j.Date > t.Date # at a later time then then it was in this
>> person's
>> posession
>> GROUP BY t.Name
>
> John was given problems A,B,C,D,E. He could solve A so gave it to
> Mark, C so gave it to Lucy, or D so gave it to Lucy. Therefore, John
> bounced 3 problems to someone else so I want to see John 3. Lucy was
> unable to solve D so gave it to Mark. Lucy did solve C. Therefore,
> Lucy bounced 1 problem. Mark was unable to solve A or D so gave them
> to Hank. Therefore, Mark should return 2.
>
> Does that help clear up what I'm trying to do?

Yup, with Mark 2 it's clear (tried to envision some logic for Mark like
'giving away a value will only count if you were the first one to own the
problem', but that obviously didn't work either ). I think the query I
gave you in the previous reply will work (untested).
--
Rik Wasmus