SQL GROUP BY HAVING

 in SQL  tagged having / group / by by

Summarizing tables with GROUP BY is very common and a must-have skill for the data analyst. This post is the first in a series of posts. To understand GROUP BY you will need to work through the series.

Let’s look at this with a very simple example.

This example is in SQL Server using SSMS. Below are some CREATE TABLE statements you can use in your non-production server. 

CREATE TABLE [dbo].[Employees](
	[EmpId] [int] NULL,
	[FName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Orders]    Script Date: 2/16/2023 10:52:16 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[OrderId] [int] NULL,
	[EmpId] [int] NULL,
	[Sales] [money] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Employees] ([EmpId], [FName]) VALUES (1, N'Sally')
GO
INSERT [dbo].[Employees] ([EmpId], [FName]) VALUES (2, N'Bob')
GO
INSERT [dbo].[Employees] ([EmpId], [FName]) VALUES (3, N'Pat')
GO
INSERT [dbo].[Orders] ([OrderId], [EmpId], [Sales]) VALUES (1, 1, 20.0000)
GO
INSERT [dbo].[Orders] ([OrderId], [EmpId], [Sales]) VALUES (2, 1, 30.0000)
GO
INSERT [dbo].[Orders] ([OrderId], [EmpId], [Sales]) VALUES (3, 3, 10.0000)
GO
INSERT [dbo].[Orders] ([OrderId], [EmpId], [Sales]) VALUES (4, 2, 15.0000)
GO
INSERT [dbo].[Orders] ([OrderId], [EmpId], [Sales]) VALUES (5, 3, 25.0000)
GO

Ok so we finally get down to our GROUP BY HAVING example query 

SELECT o.EmpId, SUM(o.Sales) AS total_sales
FROM dbo.Orders AS o 
GROUP BY o.EmpId
HAVING (SUM(o.Sales) > 30.0)

Below are the results in SSMS.

The HAVING filter is done after the grouping (SUM in this case), whereas if there was a WHERE clause, that filter would occur on the table before the grouping and summing occurred. Group By clause works on row data, but the Having clause works on aggregated data. In this case, we first sum up the sales of each employee and show only those total sales that exceed 30.

Leave a Reply