Help with this mysql statement

[edgarperez]

I am trying to get a short list of drivers who have won more than one race in my winners database and cannot get rid of the one time winners.

Here is what I have written
SELECT winner, count(winner) as total FROM allchallenges where winner not in ('PP', 'Called after 1 round (rain)', 'Postponed%') and attendance > 0 group by winner order by total desc"

this gives me a list with every driver in my database
when I change it to this
SELECT winner, count(winner) as total FROM allchallenges where winner not in ('PP', 'Called after 1 round (rain)', 'Postponed%') and attendance > 0 and total >1 group by winner order by total desc"

I get this errorWarning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/corvett1/public_html/national/winnercount.php on line 7

How can I fix this?

[Viper007Bond]

You can't do where total > 1 because total is created depending on you WHERE statements, i.e. you're defining total by itself.

And besides using PHP to do it, I'm not sure how to just return results where there is more than 1 entry. I'll have to think about it.

[iDxMan]

Perhaps try using the HAVING clause. Although that may not work on pre 5.0.2 versions.

http://dev.mysql.com/doc/mysql/en/differences-from-ansi.html


Standard SQL requires that a HAVING clause in a SELECT statement be able to refer to columns in the GROUP BY clause. This cannot be done before MySQL 5.0.2.

Note: HAVING must come after your GROUP BY and before any ORDER BY clause.

-r

[edgarperez]

AWESOME.

Having clause worked.

Thanks for the help.

http://www.corvettechallenge.info/national/winnercount.php

[Viper007Bond]

Mmm, I must learn about this HAVING. *reads up on it*