Menu
Obviously the query in the title does not work, but it might illustrate in a naive way, what I would like to do. I have a table that contains some users identified by an id column. This id is NOT unique within the database. It marks a user that may have multiple records in my table.
How can I show the whole record of all users (identified by id) that have more than 10 records in my table?
565
SQL SELECT COUNT(*) function The COUNT(*) function represents the count of all rows present in the table (including the NULL and NON-NULL values).
count(*) over() will count how many rows in your result set, in your case, because you did GROUP BY on [ID] column, which I assume it is a column with primary key (unique values and no null values), then in your case, count(*) returns same value as count(*) over () does.
With the help of the SQL count statement, you can get the number of records stored in a table.
Use having instead of where:
SELECT id
FROM (
SELECT id, COUNT(*) as cnt
FROM somewhere
GROUP BY id
HAVING cnt > 1
) temp_table
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
