select * from (table) where value is there more than 5 times

[skoff]

Hi
I want to use the select command to get some informations from my table. When a value is there more than 5 times in a column i want to show it.

table: boxscore
value:boston
column:team

it would look like something like this:
select * from boxscore where Team (is there more than 5 times).
I tried the > sign but it doesnt work because its a not a number its text..

[Demaestro]

try this and see if it return what you want.

select count(Team) from boxscore group by Team having count(Team) > 5

count(Team) will return an integer which you can then test using greater than symbols in the HAVING clause.

[edited by: Demaestro at 4:33 pm (utc) on Nov. 17, 2008]

[skoff]

it gives me :
a column name :count(Team)
and under this column i have 16 and 18?

[andrewsmd]

Are you using PHP to parse this? I would assume so since you posted in the PHP forum. If you are using PHP you could check like so. Mind you I use DB.php that is associated with pear if you are using the php mysql functions your syntax would be a little different but the logic is the same
$query = select count(Team) from boxscore where team = 'boston';
$result = $conn->query($query);
while($row = $result->fetchRow(DB_FETCHMODE_ASSOC)){

$count = $row['count(Team)'];
if($count > 5){

//do your other queries

}//if

}//count
I don't like to use mysql that much because to me it is hard to do simple logic like you are trying to do. I almost always try to parse anything that I can with PHP and just use mysql to retrieve the data.

[ngrant]

SELECT Team
FROM boxscore
GROUP BY Team
HAVING count(Team) > 5

Try that I did a test run in SQLserver with same fake data and it worked out, that should run fine in mySQL

[Demaestro]

The 16 and 18 means there is a team that has a count of 16 and one that has 18.

I shouldn't have had it return the count but return the teams.

Try it like ngrant has it.

[skoff]

yeah thanks a lot it's working! thank you guys for helping me, I have a lot of questions and I really like this forum! Thanks again!

[Demaestro]

w00t WebmasterWorld... love it too.

For every question I have answered I have asked one and had it answered.