SQL SERVER HAVING CLAUSE QUERY

SQL Server HAVING Clause Example

The HAVING clause is used to filter grouped data after using GROUP BY.

Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

Example: Find Departments Having Total Salary Greater Than 100000

Employee Table

EmpIDEmpNameDepartmentSalary
1AmitHR25000
2RaviIT40000
3NehaIT35000
4PriyaHR30000
5KaranSales45000
6SimranSales60000

SQL Query

SELECT Department, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY Department
HAVING SUM(Salary) > 100000;

Result

DepartmentTotalSalary
Sales105000

Explanation

  • GROUP BY Department → Groups employees department-wise.
  • SUM(Salary) → Calculates total salary of each department.
  • HAVING SUM(Salary) > 100000 → Displays only departments whose total salary is greater than 100000.

Difference Between WHERE and HAVING

WHEREHAVING
Filters rows before groupingFilters groups after grouping
Cannot use aggregate functions directlyCan use aggregate functions
Used before GROUP BYUsed after GROUP BY

Another Simple Example

Count Employees in Each Department

SELECT Department, COUNT(*) AS TotalEmployee
FROM Employee
GROUP BY Department
HAVING COUNT(*) >= 2;

Result

DepartmentTotalEmployee
HR2
IT2
Sales2

Comments

Post a Comment

Popular posts from this blog

STORE PROCEDURE BASIC PROGRAMMING - PART 1

  1. Create a Stored Procedure CREATE PROCEDURE sp_GreetUser AS BEGIN PRINT 'Welcome to SQL Server Stored Procedure Programming' ; END ; ================================================================================ Stored Procedure with Parameters Create Procedure CREATE PROCEDURE sp_AddNumbers @Num1 INT , @Num2 INT AS BEGIN DECLARE @ Result INT ; SET @ Result = @Num1 + @Num2; PRINT 'Addition Result = ' + CAST (@ Result AS VARCHAR ( 10 )); END ; Execute Procedure EXEC sp_AddNumbers 10 , 20 ; ========================================================================== Stored Procedure with Parameters Create Procedure CREATE PROCEDURE sp_AddNumbers @Num1 INT , @Num2 INT AS BEGIN DECLARE @ Result INT ; SET @ Result = @Num1 + @Num2; PRINT 'Addition Result = ' + CAST (@ Result AS VARCHAR ( 10 )); END ; Execute Procedure EXEC sp_AddNumbers 10 , 20 ; ======================...
Read more

Python Introduction

PYTHON PROGRAMING https://thonny.org/ Python supports multiple programming paradigms, including object-oriented, procedural, and functional programming . It provides a rich standard library and a large ecosystem of third-party packages, which helps developers to build applications faster and more efficiently. Python is platform-independent, meaning Python programs can run on Windows, Linux, and macOS without modification. Due to its flexibility and powerful features, Python is widely used in fields such as: ·          Web Development ·          Data Science & Data Analytics ·          Artificial Intelligence & Machine Learning ·          Automation & Scripting ·          Desktop Application Development · ...
Read more