Need help with DISTINCT or another approach ??

[lxndr]

I'm trying to get a count of users in a particular table but I only want to count them once for each occurrence of another field in the database, ie

name: john
year: 1958
field x: etc
field y:  etc

name: john
year: 1958
field x: etc
field y: etc

.. would only count once for the name "John" .. the output I'm looking for is:

name  occurrences
John    235
Mary    167

etc

To try and make it clearer if John appears in the name column 10 times, 3 of those have the year column yet to 1958, 4 have 1960 and 3 have 1962 then I would want to return:

John:  3

but I need to do it for all the names in the one query.


I can see how I could use DISTINCT on the year field but how do I manage to get the user count based on it ?  Or is there a better way of doing this ?

[effigy]

There may be a better way, but try this:
[s][tt]
SELECT name, COUNT(*) AS occurrences FROM (SELECT COUNT(*), name FROM [i][color=red]table[/color][/i] GROUP BY name, year) AS tmp GROUP BY name;
[/tt][/s]
[tt]
SELECT name, COUNT(*) AS occurrences FROM (SELECT DISTINCT name, year FROM [i][color=red]table[/color][/i]) AS tmp GROUP BY name;
[/tt]

[fenway]

Derived tables are definitely the way to go.

[lxndr]

[quote author=effigy link=topic=123886.msg512712#msg512712 date=1169675182]
There may be a better way, but try this:
[s][tt]
SELECT name, COUNT(*) AS occurrences FROM (SELECT COUNT(*), name FROM [i][color=red]table[/color][/i] GROUP BY name, year) AS tmp GROUP BY name;
[/tt][/s]
[tt]
SELECT name, COUNT(*) AS occurrences FROM (SELECT DISTINCT name, year FROM [i][color=red]table[/color][/i]) AS tmp GROUP BY name;
[/tt]
[/quote]

Thanks heaps, that seems to be working great.  Appreciate the help.

..