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 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 of 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.
