[SOLVED] GROUP BY or DISTINCT?

[l3asturd]

ok, I don't have any code yet, my module is a concept until I can figure out if it's possible. So here's what I have:

 

A table that has user names, golf courses, and scores at each course. There are multiple entries for each course but different scores. OK here's an example table:

 

    Name        Score      Course

-------------------------------------

John              50          Visalia

John              49          Visalia

Rick              48          Visalia

John              52          Tulare

Rick              77          Hanford

Rick              50          Tulare

 

 

OK, now, on my stats page, I wanted users to be able to see their score averages for each course. I know how to pull the data from SQL and GROUP BY course. I know how to AVG(Score) AS TotalAverage.  Problem is, how can I average scores for each course AND for each player at the same time.

 

John's average score at Visalia is 50

John's average score at Tulare is 52

 

See what I'm getting at. I tried something like:

 

SELECT * from TABLE WHERE name=John GROUP BY course AVG(score) AS courseaverage

 

but this is bad syntax, and wouldn't result in the data I'm looking for. Any ideas?

 

 

[teng84]

 

SELECT name,avg(score),course from TABLE WHERE name=John GROUP BY course

[l3asturd]

 

SELECT name,avg(score),course from TABLE WHERE name=John GROUP BY course

 

I think that worked, I wrote a quickie to find out, and it looks like it's giving me the results I'm asking for. I'll write back after I've tested this out with real data.

[teng84]

YEAH CHEERS BUT YOUR IN THE WRONG FORUM THIS IS PHP NOT SQL 

[l3asturd]

YEAH CHEERS BUT YOUR IN THE WRONG FORUM THIS IS PHP NOT SQL 

 

Yeah it works. Sorry, it all kinda runs together after looking at code all day. PHP...SQL...HTML...

[fenway]

You almost never want distinct... except inside a COUNT().

[sphinx9999]

You almost never want distinct...

Why do you almost never want distinct? I'm trying to find out which is best (performance-wise) - group by or distinct. seems to be a big debate on this with most people saying they are both identical. your comment suggests you think there is a difference.

i.e.

SELECT DISTINCT id FROM phrase WHERE campaign LIKE 'proper%'

versus

SELECT id FROM phrase WHERE campaign LIKE 'proper%' GROUP BY advert_id

[fenway]

When there's just one column it doesn't matter.