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




Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str