Skip to main content

GROUP BY clause in PostgreSQL

With the assistance of some functions, the GROUP BY statement in SQL is used to organize identical data into groups. i.e. if a specific column has the same values in distinct rows, then these rows will be arranged in a group. 

Syntax: the syntax of the GROUP BY clause is given below.


SELECT <Col_1>, <Col_2>, <aggregate_function(Col_3)>....<Col_n>
FROM <table_name>
WHERE <conditions>
GROUP BY <Col_1>, <Col_2>....<Col_N>


For each the group you can apply an aggregate function like SUM(), COUNT(), etc. The GROUP BY clause appears after FROM or WHERE clause in a SELECT statement.

PostgreSQL GROUP BY clause examples

Suppose we have the following table. You can create an Employee table using the below PostgreSQL statement.

CREATE TABLE public."Employee"
(
    "Id" bigserial NOT NULL,
    "FName" text COLLATE pg_catalog."default",
    "LName" text COLLATE pg_catalog."default",
    "UserName" text COLLATE pg_catalog."default",
    "Password" text COLLATE pg_catalog."default",
    "Contact" text COLLATE pg_catalog."default",
    "Salary" numeric NOT NULL DEFAULT 0.0,
    "DOJ" date NOT NULL,
    CONSTRAINT "Employee_Primarykey" PRIMARY KEY ("Id")



Insert some records in "Employee" table


INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary", "DOJ")
                VALUES ('Name1','last1', 'name1@gmail.com', 'name@0123', '9088877787', '20000','2018/07/07');

INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary", "DOJ")
                VALUES ('Name2','last2', 'name2@gmail.com', 'name@0123', '9288877787', '30000','2018/07/07');
              
INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary", "DOJ")
                VALUES ('Name3','last3', 'name3@gmail.com', 'name@0123', '9388877787', '40000','2017/09/08');

INSERT INTO public."Employee"(
                "FName", "LName", "UserName", "Password", "Contact", "Salary", "DOJ")

                VALUES ('Name4','last4', 'name4@gmail.com', 'name@0123', '9344877787', '70000','2018/06/09');


Illustrates of PostgreSQL GROUP BY clause.

1-     Using PostgreSQL GROUP BY without an aggregate function example.

Here I am applying GROUP BY clause on the columns DOJ (date of joining) because it has identical data

SELECT "DOJ"
                FROM public."Employee"

                GROUP BY "DOJ"



2-     Using PostgreSQL GROUP BY with an aggregate function example.

Suppose, I want to count the employees who have joined the company on the same date so we can query the data from the table using the following statement.

SELECT "DOJ", COUNT(*)
    FROM public."Employee"

                GROUP BY "DOJ"



You can see in the result on dated '2018/07/07', there are two joinings.


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