Need help with count query

[sledge4]

Hi,

I'm having some trouble with a query. Maybe someone could shed some light?

I have a table with the following data..

ID | iFin | iOwner
1 | 1 | Tom
2 | 3 | Dave
3 | 1 | Tom
4 | 5 | Will
5 | 3 | Dave

Trying to the get the data to output as...

iOwner| Races | Win | Place | Third | Fourth | Fifth
Tom | 2 | 2 | 0 | 0 | 0 | 0
Dave | 2 | 0 | 0 | 2 | 0 | 0
Will | 1 | 0 | 0 | 0 | 0 | 1

Essentially i'm counting how many 1's a name has and also how many total times the name appears.

So in the case of Tom, his name occurs 2 times and he has two 1's and 0 of any other number.

Dave's name shows twice, and has two 3's.

Hope this makes sense.

Any questions let me know.

Thanks in advance!

Will

PS - Here's a copy of the query I have now. All it does correctly is count the number of times each iOwner appears. Everything else just outputs 0's.

SELECT DISTINCT '', iOWNER, COUNT(iOWNER) AS Races,
(SELECT COUNT(iFIN) FROM racestats
WHERE iOWNER = '' AND iFIN = '1') win,
(SELECT COUNT(iFIN) FROM racestats
WHERE iOWNER = '' AND iFIN = '2') place,
(SELECT COUNT(iFIN) FROM racestats
WHERE iOWNER = '' AND iFIN = '3') third,
(SELECT COUNT(iFIN) FROM racestats
WHERE iOWNER = '' AND iFIN = '4') fourth,
(SELECT COUNT(iFIN) FROM racestats
WHERE iOWNER = '' AND iFIN = '5') fifth
FROM racestats
GROUP BY iOWNER, win, place, third, fourth, fifth
ORDER BY Races DESC

[Coyote6]

You should use a separate table to store all of the racers names and then have a separate table for races and another table for their place in the race, so you are looking at three different tables.

And just for fun I'm going to throw one in for different tracks.


Racers
id | name
1 | Tom
2 | Dave
3 | Will

Tracks
id | name
1 | Wario's Stadium
2 | Yoshi's Maze

Races
id | track_id | date
1 | 1 | 2009-11-01
2 | 2 | 2009-11-01
3 | 1 | 2009-11-02

Racer_Placement
id | racer_id | race_id | place
1 | 1 | 1 | 1
2 | 2 | 1 | 3
3 | 1 | 2 | 1
4 | 2 | 2 | 3
5 | 3 | 2 | 5


This will allow you to search on many more methods.

To get the results you want:

SELECT r.name as Racer, (SELECT COUNT(*) FROM Racer_Placement as rp WHERE rp.racer_id=r.id) as Races, (SELECT COUNT(*) FROM Racer_Placement as rp WHERE rp.racer_id=r.id && rp.place=1) as Wins, (SELECT COUNT(*) FROM Racer_Placement as rp WHERE rp.racer_id=r.id && rp.place=2) as Place, (SELECT COUNT(*) FROM Racer_Placement as rp WHERE rp.racer_id=r.id && rp.place=3) as Third, (SELECT COUNT(*) FROM Racer_Placement as rp WHERE rp.racer_id=r.id && rp.place=4) as Fourth, (SELECT COUNT(*) FROM Racer_Placement as rp WHERE rp.racer_id=r.id && rp.place=5) as Fifth
FROM Racers;


Old Pedant or someone else will probably have a more optimized way of accomplishing this.

But the neat part is now you can search by track or date or by specific racer, by adding a little to the query. (Not sure on the exact syntax and order here but it is possible to do.)

-- Places by track name.
...
JOIN Racer_Placement as rpl ON (rpl.racer_id=r.id) JOIN Race as rc ON (rpl.race_id=rc.id) JOIN Tracks as t ON (rc.track_id=t.id)
WHERE t.name='Wario\'s Stadium';

-- Or I believe will accomplish the same.
...
JOIN Racer_Placement as rpl, Race as rc, Tracks as t ON (rpl.racer_id=r.id && rpl.race_id=rc.id && rc.track_id=t.id)
WHERE t.name='Wario\'s Stadium';

-- Places by date.
...
, Racer_Placement as rpl, Race as rc ON (rpl.racer_id=r.id && rpl.race_id=rc.id)
WHERE rc.date='2009-11-02';

-- Places by racer.
...
WHERE r.name='Tom';

[sledge4]

Your ideas put me in the direction and it is now working.

Thanks!