david david - 4 months ago 8
SQL Question

Only show data that is in database more than 5 times

I have a report that works fine apart from my final hurdle.

Table Example, Row A:

John
John
Mike
John
Steve
Steve


I only want my report to show name that are in my table 5 times or more. I'm struggling to work out what criteria I need to set

Any advice?




from comment:

SELECT tblSourceData.Athlete, Max(tblSourceData.Swim) AS MaxOfSwim,
Max(tblSourceData.Bike) AS MaxOfBike, Max(tblSourceData.Run) AS MaxOfRun
FROM tblSourceData
GROUP BY tblSourceData.Athlete, tblSourceData.Gender
HAVING (((tblSourceData.Athlete)>="5") AND ((tblSourceData.Gender)="f"));

Answer

You need a HAVING clause.

SELECT aName, Count(aName) AS NumName
FROM myTable
GROUP BY aName
HAVING Count(aName)>=5

Edit

you probably want

SELECT tblSourceData.Athlete, Max(tblSourceData.Swim) AS MaxOfSwim, 
    Max(tblSourceData.Bike) AS MaxOfBike, Max(tblSourceData.Run) AS MaxOfRun 
FROM tblSourceData 
WHERE tblSourceData.Gender="f"
GROUP BY tblSourceData.Athlete 
HAVING Count(tblSourceData.Athlete)>=5

Criteria that don't use aggregated columns go into the WHERE clause.

Comments