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
>
> In this case:
> foo A 01-01-01
> bar A 01-01-02
> foo A 01-01-03
> foz A 01-01-02
> foo A 01-01-05
>
> - this query would still consider A been given away by foo, never mind
> that it is now back in foo's posession (this could be taken care of using
> a second join for that though, checking wether the owner of the value with
> the latest date is equal to this one)
> - it will still count as A been given, so a score of 1 for that,
> irregardless wether foo has given it away once or several times. If every
> 'change of ownership from foo to someone else' should be counted as a
> seperate 'give' action, 't will be a true join fest deluxe...)
> --
> Rik Wasmus- Hide quoted text -
>
> - Show quoted text -

Ok, using my original 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


oops, I had a typo in the results above, Mark should be 2.

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? Thanks1