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.


PostgreSQL: Function and its uses

The function is the set of PostgreSQL statements that stored on the database server and can be invoked using the SQL interface. PostgreSQL ...