Use of Having clause in SQL server example

SQL HAVING Clause

Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

 First Create a Table called [dbo].[Employee_Test]

CREATE TABLE [dbo].[Employee_Test](

      [Emp_ID] [int] NOT NULL,

      [Emp_name] [varchar](100)NOT NULL,

      [Emp_Sal] [int] NULL,

      [Age] [int] NULL,

      [Gender] [nchar](10)NOT NULL

         [DeptId] [int] NOT NULL

) ON [PRIMARY]

Now Insert some data in [dbo].[Employee_Test]

INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])

 VALUES (1, N'Anees', 1000, 30, N'Male', 101)

INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])

 VALUES (2, N'Rinku', 1200, 45, N'Male', 101)

INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])

 VALUES (3, N'John', 1100, 25, N'Male', 101)

INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])

 VALUES (4, N'Stephen', 1300, 30, N'Male', 101)

INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])

 VALUES (5, N'Maria', 1400, 45, N'Female', 101)

INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])

 VALUES (6, N'Ankit', 1000, 25, N'Male', 101)

INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])

VALUES (7, N'Renu', 1200, 30, N'Female', 101)

Now create a department table

CREATE TABLE [dbo].[tblDept](

       [DeptId] [int] NULL,

       [DeptName] [varchar](50) NULL,

       [DeptLocation] [varchar](50) NULL

)

And Insert some data into Dept table

USE [Employee]

GO

INSERT INTO [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])

 VALUES (101, N'Development', N'Bangluru')

GO

INSERT INTO [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])

VALUES (102, N'Finance', N'Delhi')

GO

INSERT [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])

 VALUES (103, N'HR', N'Hyderabad')

GO

INSERT INTO [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])

VALUES (104, N'Sales', N'Delhi')

GO

INSERT INTO [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])

 VALUES (105, N'Training', N'Bangluru')

GO

Now Data in [dbo].[Employee_Test]

Now Data in [dbo].[tblDept]

Records Without Having Clause

select dp.DeptName, Max([Emp_Sal]) from [dbo].[Employee_Test] emp

inner join [dbo].[tblDept] dp on emp.DeptId=dp.DeptId group by dp.DeptName

Records With Having Clause

select dp.DeptName, Max([Emp_Sal]) from [dbo].[Employee_Test] emp

inner join [dbo].[tblDept] dp on emp.DeptId=dp.DeptId group by dp.DeptName

 having Max([Emp_Sal])>1000

Note:-

When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement, the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped based on the GROUP BY clause.

Finally, any conditions on the group functions in the HAVING clause are applied to the grouped rows before the final output is displayed.

How to use Group By Clause in sql server ?

GROUP BY Clause is used in a select statement to collect data across multiple records and group the results by one or more columns.or we can say that.Group By clause is used for grouping the records of the database tables.The  Group By clause creates a single row for each group and this process is called aggregation. Using for  group by clause we have to use at least one aggregate function in Select statement. aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

Syntax of Group By
                                  Select Column1,Column2,Column3 from Table_Name
                                  Where Condition
                                  Group By Column1,.........   OR

                                  Select Column1,Column2,Column3 from Table_Name
                                  Where Condition
                                  Group By Column1,.........
                                  Order By Column2...... OR

                                  Select Column1,Column2,SUM(Column3)as Total from Table_Name
                                  Where Condition
                                  Group By Column1,......... 

 -- Create table in database first:-

CREATE TABLE [dbo].[Employee_Test](

      [Emp_ID] [int] NOT NULL,

      [Emp_name] [varchar](100)NOT NULL,

      [Emp_Sal] [int] NULL,

      [Age] [int] NULL,

      [Gender] [nchar](10)NOT NULL

) ON [PRIMARY]

--Now insert records into database table 

INSERT  INTO  [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender])

 VALUES (1, N'Anees', 1000, 30, N'Male      ')

INSERT  INTO  [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender])

 VALUES (2, N'Rinku', 1200, 45, N'Male      ')

INSERT  INTO  [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender])

 VALUES (3, N'John', 1100, 25, N'Male      ')

INSERT  INTO  [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender])

 VALUES (4, N'Stephen', 1300, 30, N'Male      ')

INSERT  INTO  [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender])

 VALUES (5, N'Maria', 1400, 45, N'Female    ')

INSERT  INTO  [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender])

 VALUES (6, N'Ankit', 1000, 25, N'Male      ')

INSERT  INTO  [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender])

 VALUES (7, N'Renu', 1200, 30, N'Female    ')

--Select Query to select all records without Group by clause

Select Query Group by Gender

Select Query Group by Age

Select Query Group by Age and using having clause also