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.