A Script A Day - Day 21 - The HAVING Clause

Today’s post is about the HAVING clause which specifies a search condition for a group or an aggregate. HAVING is typically used in a GROUP BY clause and the two biggest uses I have for HAVING are SUM() for reporting and COUNT() for hunting down invalid data.  In the script below we create a HavingBlog table and insert some test data.  We then return a count of server names by data centre where the COUNT(ServerName) > 1.

Now, yes I know in an ideal world this would never happen!  You wouldn’t have two servers with the same name in the same data centre, this is where constraints come into play.  In reality not all functional dependancies are as obvious as this and some are not catered for during the design phase, when this is the case we need to have a look into a problem which is where HAVING can help us out.

/*

      -----------------------------------------------------------------

      HAVING Clause

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE SQLServer365;

GO

--Create test table

IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID('[dbo].[HavingBlog]') AND [type] = 'U')

DROP TABLE [dbo].[HavingBlog];

GO

CREATE TABLE HavingBlog

      (

            HavingBlogID INT IDENTITY (1,1),

            DataCentre VARCHAR(30),

            ServerName VARCHAR(15)

      );

GO

-- Insert test data

INSERT INTO HavingBlog

VALUES ('Manchester','MAN-LIVE-SQL01');

INSERT INTO HavingBlog

VALUES ('Manchester','MAN-LIVE-SQL02');

INSERT INTO HavingBlog

VALUES ('Manchester','MAN-LIVE-SQL02');  

INSERT INTO HavingBlog

VALUES ('London','LON-LIVE-SQL01');

INSERT INTO HavingBlog

VALUES ('London','LON-LIVE-SQL02');

INSERT INTO HavingBlog

VALUES ('London','LON-LIVE-SQL03');

INSERT INTO HavingBlog

VALUES ('Nottingham','NOT-LIVE-SQL01');

GO

-- Return count of servernames by data centre

SELECT

      DataCentre,

      ServerName,

      COUNT(ServerName) AS [NumberOfServers]

FROM

      SQLServer365.dbo.HavingBlog

GROUP BY DataCentre, ServerName

HAVING COUNT(ServerName) > 1;

GO

Enjoy!

Chris