SQL- Group by Having Clause

In database systems many times you need to summarize the data stored in tables in answer to adhoc queries of the users. For this SQL Aggregate Functions can be used. But if your need to summarize the data on the basis of some group of values, then you must understand SQL- Group by Having Clauses

What is SQL GROUP BY HAVING?

To understand SQL Group By Having first understand where it can be applied. Here are a few situations where you might need to summarize data for groups

  • Show total salary of employees in different departments.
  • List total salary paid to different jobs in the orgnaization.
  • Display average marks scored by students in different subjects.
  • Find out the maximum score of students in different courses.
  • Display average sales done at different locations of an organizations.
  • Show the total earnings of an organization month wise
  • List total earnings of an organization month wise but when the order quantities are more than 700

You might have faced similar queries to be answered for your organizational needs. For such queries you will need the GROUP BY HAVING clause to be added in the SELECT Statement.

Syntax of SELECT statement with SQL GROUP BY HAVING Clauses

SELECT column-name(s), group function(s)

FROM table-name

[WHERE condition(s)]

[GROUP BY column-name]

[HAVING Group-condition]

In this statement you have to write column names and group functions  to aggregate values after SELECT keyword. The WHERE clause is used to filter those rows which you want to include in summarized data. It means that WHERE clause filters the rows before applying the aggregate function. GROUP BY clause includes the column names for which you want to group the data showing the aggregate values. HAVING clause filters the final data after grouping and aggregating. It applies condition on aggregate functions’ output.

Points to ponder:

  • The non aggregate column names in the SELECT clause must be included in the GROUP BY clause.
  • The HAVING clause can include aggregate function other than those used in the SELECT clause.

Example

SELECT Job_Desc, SUM(Salary)

FROM Emp_Table

WHERE Deptt_id IN (‘sales’,’HR’,’Accounts’,’Training’)

GROUP BY Job_Desc

HAVING SUM(Salary)>10000

The above query will display total salaries for different Job descriptions from Employees table filtering data for sales, HR, Accounts and Training departments. The final results will display only those job descriptions whose total salary is greater than 10000.

Example Queries for Group By Having

The orders table is created with the data as shown below

ORDNO MONTH CID AID PID QTY DOLLARS
1011 jan c001 a01 p01 1000 450
1012 jan c001 a01 p01 1000 450
1019 feb c001 a02 p02 400 180
1017 feb c001 a06 p03 600 540
1018 feb c001 a03 p04 600 540
1023 mar c001 a04 p05 500 450
1022 mar c001 a05 p06 400 720
1025 apr c001 a05 p07 800 720
1013 jan c002 a03 p03 1000 880
1026 may c002 a05 p03 800 704
1015 jan c003 a03 p05 1200 1104
1014 jan c003 a03 p05 1200 1104
1021 feb c004 a06 p01 1000 460
1016 jan c006 a01 p01 1000 500
1020 feb c006 a03 p07 600 600
1024 mar c006 a06 p01 800 400

Example 1

Find the total dollars earned month wise.

Query

select month, sum(dollars)
from orders
group by month

Output

MONTH SUM(DOLLARS)
apr 720
mar 1570
may 704
jan 4488
feb 2320

Example 2

Display the dollars earned month wise when the total dollars earned are greater than 700.

Query

select month, sum(dollars)
from orders
group by month
having sum(dollars)>750

Output

MONTH SUM(DOLLARS)
mar 1570
jan 4488
feb 2320

Example 3

Display the dollars earned month wise when the total dollars earned are greater than 700 but only for those orders where quantity ordered is greater than 500 units

Query

select month, sum(dollars)
from orders
where qty>500
group by month
having sum(dollars)>750

Output

MONTH SUM(DOLLARS)
jan 4488
feb 2140

Example 4

Display the total dollars earned month wise when the average quantity ordered is greater than 800 but only for those orders where quantity ordered is greater than 500 units

Query

select month, sum(dollars)
from orders
where qty>500
group by month
having avg(qty)>800

Output

MONTH SUM(DOLLARS)
jan 4488

These queries are executed in Oracle Live. If you don’t have Oracle Desktop version, you can try here by signing up.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *