[RESOLVED] Count Problem

[esiason14]

I'm having a problem with COUNT/GROUP BY limiting my results.

I'm dealing with football stats. I want to find the number of times a quarterback threw for 300 yards in a season. The problem is I want to also return each week in which he did so.

For example:

SELECT count(pass_yd) as totweek, p.position_id, pos.position_id, p.picture_loc, pos.position_abbv, p.lname, p.fname, ps.week
FROM playerstats ps, players p, positions pos 
WHERE ps.year=2007 
AND p.player_id=ps.player_id 
AND p.position_id=1 
AND  ps.pass_yd>299 
AND p.position_id=pos.position_id 
GROUP by ps.player_id 
ORDER by totweek DESC;

This query returns the correct count, but only shows the last week where he threw for 300 yards. For example if a player did this 3 times (in weeks 1,3,5). I want it to return count=3. weeks=1,3,5. Currently it would return count=3, week=5

I want to return ALL the week.

Any help would be appreciated. Thanks

[bradgrafelman]

Instead of selecting ps.week, try this:

GROUP_CONCAT(ps.week SEPARATOR ',') AS week

You may also want to add an ORDER BY clause so that the weeks are in order. More info on GROUP_CONCAT() can be found here.

[esiason14]

Perfect. Thank you!