Skip to main content

HAVING clause in PostgreSQL

In this article, we will learn about 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 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, means 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 till 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