Question[SQL]：Can you use a SQL statement to finding duplicate values!

Question：Can you use a SQL statement to finding duplicate values!
How can I find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname   number_dups
---------------------------------------- -----------
Ringer  2

(1 row(s) affected)

 

Answer：

    if OBJECT_ID('dbo.Table101302') is not null
        drop table dbo.Table101302
    create table dbo.Table101302
    (
        [Id] int,
        [au_lname] varchar(10)
    )
    insert into dbo.Table101302 values(1, 'name1')
    insert into dbo.Table101302 values(2, 'name2')
    insert into dbo.Table101302 values(3, 'name3')
    insert into dbo.Table101302 values(4, 'name3')
    insert into dbo.Table101302 values(5, 'name4')
    insert into dbo.Table101302 values(6, 'name5');
    insert into dbo.Table101302 values(7, 'name5');
    
    select [au_lname], [number_dups]=COUNT(1)
        from dbo.Table101302
        group by [au_lname]
        --having(COUNT(1)>1)

    drop table dbo.Table101302