HAVING clause in PostgreSQL


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



NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...