Counting records

Hello,

I want to count some records in a table I have. My table is updated when someone posts information to an .asp page. Then, I use SSRS to create reports on this information in the table. I want to filter the results that the report shows based on how many records are in the table.

For instance:

Name last4 ID

John 2112 54432

John 3222 21223

John 7777 88888

John 3333 22222

John 3212 88722

Carol 2122 12111

Carol 5555 12111

Carol 3342 83635

Carol 1211 98363

Steve 2122 21331

James 2113 21123

Teresa 3223 21154

I want to filter the results shown in the report to records with > 3 occurances. So, the results for the above table would show only Carol and John.

Is there any way I can do this either in the SQL statement or in the reporting services statement?

Any help is appreciated.

Thanks

I think this ought to meet your needs

SELECT Name, last4, ID

FROM Some_Table

WHERE Name IN (

SELECT Name

FROM Some_Table

GROUP BY Name

HAVING COUNT(*) > 3

)

Just replace Some_Table with your table name.|||

Great. Thanks

I had something similar, but I had a few parts backwards. : )

So, if I wanted to add some more parameters should those go after the HAVING?

Like: HAVING COUNT (*) >3 AND Date BETWEEN @.startdate AND @.enddate

Thanks again.

|||Typically you would put the predicates that doesn't have any aggregate functions or GROUPING function in the WHERE clause. Logically the HAVING clause is seen as being evaluated after the GROUP BY clause has been evaluated so that the aggregate function results can be compared. This is different from the WHERE clause which can be evaluated at different stages (join conditions, table / index scans / seeks etc). So put the Date predicate in the WHERE clause and the COUNT(*) has to be in the HAVING clause.|||

So, if I understand correctly, it should be something like this:

SELECT Name, last4, ID

FROM Some_Table

WHERE Name IN (

SELECT Name

FROM Some_Table

GROUP BY Name

HAVING COUNT(*) > 3

) AND Date BETWEEN @.begindate AND @.enddate

Would that be the correct placement to include the date range in the WHERE clause?

Thanks again.