DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply GROUP BY with HAVING?

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:

City State Country Vendors
Lemon Grove Arizona United States 2
Altadena California United States 2
Berkeley California United States 2
Burbank California United States 2
Burlingame California United States 3
Concord California United States 2

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE