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


NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...