SQL Day 11: Group By in SQL Server

LO1: Grouping rows using Group By
LO2: Filtering Groups
LO3: Difference between Where and Having Clause

Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions.

Let’s say we have a table tblEmployee which contains all the salary of employees including the cities.



So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees.

select MAX(salary) from tblemployee



In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions.

select MIN(salary) as [Minimum Salary] from tblemployee
select SUM(salary) as [Total Salary] from tblemployee



Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group By clause.

select city, SUM(salary) from tblemployee Group By city



If you remove the Group By clause from above statement then SQL will throw an error because in the query we are not grouping the cities so the column we used in a select statement that has to satisfy one or two conditions.

You should either have an aggregate function applied to a column or that column should be the part of Group By clause

Now if we need to breakdown by the employee count also then we need to add the count aggregate function in the select statement.

select city, SUM(salary), COUNT(id) as [Total Employees]
from tblemployee Group By city


Filtering rows using Having Clause

Having clause is used to filter the rows and always place after the Group By clause.
Let’s say we need to filter the tblemployee table as per the gender id so to perform this we need to use Having clause in our query.

select city, SUM(salary), COUNT(id) as [Total Employees]
from tblemployee Group By city, genderid
Having genderid=1


Difference between Where and Having Clause

  • Where clause filters the rows before aggregation, whereas Having filter the groups after the aggregations are performed.
  • Where clause can be used with SELECT, UPDATE and DELETE statement, whereas Having can only be used with SELECT statement.
  • The aggregate function can be used in Having clause whereas aggregate function cannot be used in where clause unless it is in a subquery contained in a Having clause.

That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading