Count and filter.

[yeppy12]

I have a table with 3 columns: name, id, and stamp. I want to count the number of times the person's name appears in the table and display only the ones that appear more than 3 times. This query seems so simple, but it only shows me 3 people when I know there are many more. Also, the query shows those 3 people several times each...so how do I eliminate the duplicates?

 

[aikea]

select id, count(name) as name_count from your_table_name group by id having name_count > 3;

 

[yeppy12]

I used your query and a box pops up asking me to enter a parameter for name_count. What does that mean, and what should I enter?

 

[DevastatioN]

Create a query in design mode and add the table that id, name, and stamp appears in.

Add the "name" field to the query twice, and click the "Totals" button on the top toolbar (the one with sumation symbol Sigma).

Under the second "name" field in the query select "Count" under the Totals: option, and put the criteria as ">3". This should list the names of everyone who appears more than 3 times in the table.

Your query should look like this

Entry 1: (Field: name, Total: Group By, Criteria: )
Entry 2: (Field: name, Total: Count, Criteria: >3)

Note that if you add other fields in the query, this might not work if the data in the other fields is unique (such as if Joe had stamp 001 in one record, but stamp 002 in another record)

 

[aikea]

select count(id), name from your_table_name group by name having count(id) > 3;