In this article, we will learn about the
HAVING clause, its proper use on the scenarios based, how to eliminate groups
of rows that do not satisfy a specified condition, etc. The HAVING clause used
with the GROUP BY clause, and it behaves like where clause but applies only to
groups as a whole. The HAVING clause is applied to the rows in the result set,
which means the first data is fetched from memory to result set then apply
HAVING clause on the row of the result set. We can apply a HAVING clause only to
columns that also appear in the GROUP BY clause or in an aggregate function.
Syntax: PostgreSQL HAVING clause
|
SELECT
col_1,
aggregate_function (col_2)
FROM
table_name
GROUP
BY
col_1
HAVING
condition;
|
Some
scenarios where we need to apply the having clause
Suppose we have a table where we manage the
monthly salary data of employees, for practice, please execute the following
PostgreSQL statement.
CREATE TABLE public."Salary"
(
"Id"
bigserial NOT NULL ,
"EmpId"
bigint NOT NULL,
"NetPayment"
decimal NOT NULL,
"TDS"
decimal NOT NULL,
CONSTRAINT "Salary_Primarykey" PRIMARY KEY ("Id")
);
|
Insert some data executing the following query.
INSERT INTO public."Salary"("EmpId","NetPayment","TDS","DateOfPayment")
VALUES(1,20000,200,'2019/01/01');
INSERT INTO public."Salary"("EmpId","NetPayment","TDS","DateOfPayment")
VALUES(2,40000,600,'2019/01/01');
INSERT INTO public."Salary"("EmpId","NetPayment","TDS","DateOfPayment")
VALUES(3,50000,400,'2019/01/01');
INSERT INTO public."Salary"("EmpId","NetPayment","TDS","DateOfPayment")
VALUES(1,20000,200,'2019/02/01');
INSERT INTO public."Salary"("EmpId","NetPayment","TDS","DateOfPayment")
VALUES(2,40000,600,'2019/02/01');
INSERT INTO public."Salary"("EmpId","NetPayment","TDS","DateOfPayment")
VALUES(3,50000,400,'2019/02/01');
|
Illustrates of PostgreSQL HAVING clause
1- Suppose, I want those EmpId who paid TDS more than 500 To
date, then I will execute the following query.
SELECT "EmpId", SUM("TDS") AS All_TDS
FROM public."Salary"
GROUP BY "EmpId"
HAVING SUM("TDS")>500
|
2- Same like above illustrate I want to those EmpId who paid
TDS less than 500, then I will execute the following query.
SELECT "EmpId", SUM("TDS") AS All_TDS
FROM public."Salary"
GROUP BY "EmpId"
HAVING SUM("TDS")<500
|
