Monday 27 February 2012

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

No comments:

Post a Comment