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. Right now I do this with 2 queries:

Doing this in perl, first I use this to populate an array of Value's:
SELECT Value FROM TABLE GROUP BY Value HAVING COUNT(*) > 1 ORDER BY
Value, Date

then I use this to cycle through each Value to get the Names

SELECT Name FROM TABLE WHERE Value=? ORDER BY Date DESC

I increment a counter (hash) for each Name except for the first row,
since the last guy to get it didn't give it to someone else.

Make sense?

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

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 clear up what I'm trying to do? Thanks1

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