On Wed, 31 Oct 2007 16:55:21 +0100, <jimnl69@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