Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the use of GROUP BY with HAVING which returns only groups with more than one population (count):

-- SELECT INTO create test table
USE tempdb;
SELECT ProductName=Name, ProductNumber, ListPrice, Color
INTO Product
FROM AdventureWorks2008.Production.Product
-- (504 row(s) affected)
 
-- INSERT 5 duplicates
INSERT Product
SELECT TOP(5) Name, ProductNumber, ListPrice, Color
FROM AdventureWorks2008.Production.Product ORDER BY NEWID()
(-- 5 row(s) affected)
 
-- SQL find duplicates by HAVING clause
SELECT ProductName, ProductNumber, ListPrice, Color
FROM Product
GROUP BY ProductName, ProductNumber, ListPrice, Color
HAVING COUNT(*) > 1
GO
/*
ProductName                   ProductNumber     ListPrice   Color
HL Touring Frame - Yellow, 50 FR-T98Y-50        1003.91     Yellow
Lock Nut 18                   LN-9161           0.00        NULL
ML Mountain Frame - Black, 48 FR-M63B-48        348.76      Black
Road-550-W Yellow, 38         BK-R64Y-38        1120.49     Yellow
Touring-3000 Yellow, 58       BK-T18Y-58        742.35      Yellow
*/
DROP TABLE tempdb.dbo.Product
GO

-- SQL group by having - t sql group by having clause

USE AdventureWorks;

GO

SELECT  A.City, SP.Name AS [State], CR.Name AS Country,

Vendors = COUNT(*)

FROM Purchasing.Vendor AS V

    INNER JOIN Purchasing.VendorAddress AS VA

            ON VA.VendorID = V.VendorID

    INNER JOIN Person.[Address] AS A

            ON A.AddressID = VA.AddressID

    INNER JOIN Person.StateProvince AS SP

            ON SP.StateProvinceID = A.StateProvinceID

    INNER JOIN Person.CountryRegion AS CR

            ON CR.CountryRegionCode = SP.CountryRegionCode

GROUP BY  A.City, SP.Name, CR.Name

HAVING COUNT(*) > 1

ORDER BY Country, [State], City

GO

Partial results: