Menu
I need to query my database to show the records inside my table where lastname occurs more than three times. Example: in my Students Table, there are 3 people with Lastname 'Smith', 4 with 'Johnson', and 1 with 'Potter'. My query should show the records of those with the lastnames Smith, and Johnson since these values occur more than or equal to 3 times.
Can anyone point me to this? I was thinking of using COUNT() but I can't seem to think how to apply it?
897
One way to find duplicate records from the table is the GROUP BY statement. The GROUP BY statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has the same values in different rows then it will arrange these rows in a group.
All you need to do is use the multiplication operator (*) between the two multiplicand columns ( price * quantity ) in a simple SELECT query. You can give this result an alias with the AS keyword; in our example, we gave the multiplication column an alias of total_price .
From Oracle (but works in most SQL DBs):
SELECT LASTNAME, COUNT(*) FROM STUDENTS GROUP BY LASTNAME HAVING COUNT(*) >= 3 P.S. it's faster one, because you have no Select withing Select methods here
193
For SQL Server 2005+
;WITH T AS ( SELECT *, COUNT(*) OVER (PARTITION BY Lastname) as Cnt FROM Students ) SELECT * /*TODO: Add column list. Don't use "*" */ FROM T WHERE Cnt >= 3 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
