Hello. In this tutorial, we will learn WHERE, GROUP BY, HAVING, and ORDER BY clauses in SQL and will use the postgresql database running on Docker.
1. Introduction
SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –
- Parsing – Process to check the SQL query syntax
- Binding – Process to check the SQL query semantics
- Optimization – Process to generate the SQL query execution plan
1.1 Usage of SQL
Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –
- Execute queries against the database
- Retrieve data from the database
- Insert new records into the database
- Update existing records into the database
- Created stored procedures, functions, and materialized views in the database
- Create users and grant permissions
- Set permissions on tables, stored procedures, functions, and materialized views
1.2 What is the WHERE clause?
The WHERE clause in a sql statement is used to filter the records based on the specified condition. It is represented by the below syntax in the sql query –
WHERE syntax
SELECT column1, column2, … column FROM table_name WHERE condition;
1.3 What is the GROUP BY clause?
The GROUP BY clause in a sql statement is used to group similar data. The GROUP BY clause is used after the WHERE clause and before the ORDER BY clause if any. It is represented by the below syntax in the sql query –
GROUP BY syntax
SELECT column1, column2, … column FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
1.4 What is the HAVING clause?
The HAVING clause in a sql statement is used to filter the records from a group according to a specified condition. It is represented by the below syntax in the sql query –
HAVING syntax
SELECT column1, column2, … column FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
It differs from the WHERE clause as below –
| HAVING clause | WHERE clause |
| Filter the records from a group according to a specified condition | Filter the records according to a specified condition |
| Executed with the GROUP BY clause in a sql statement | Executed without the GROUP BY clause in a sql statement |
| Can use aggregate functions in a sql statement | Cannot use aggregate functions in a sql statement |
| Can only be used with SELECT sql statement | Can be used with SELECT, UPDATE, and DELETE sql statements |
| Used after GROUP BY clause | Used before GROUP BY clause |
| It is a post-filter | It is a pre-filter |
| It is used to filter groups | It is used to filter single record(s) |
1.5 What is the ORDER BY clause?
The ORDER BY clause in a sql statement is used to sort the data in ascending or descending order based on one or more columns. The default sorting order is ascending. It is represented by the below syntax in the sql query –
ORDER BY syntax
SELECT column1, column2, … column FROM table_name ORDER BY column_name(s) ASC | DESC;
2. Where, Group by, Having, and Order by clause in SQL
Let us dive into some practice implementation on the postgresql database.
2.1 Pre-requirement – Postgres Setup
Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.
Docker commands
-- command to run postgres on docker -- -- remember to change the password -- docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres -- command to stop the Postgres docker container -- docker stop postgres -- command to remove the Postgres docker container -- docker rm postgres
Remember to enter the password of your choice. If everything goes well the postgresql database server would be up and running on a port number – 5433 and you can connect with the Dbeaver GUI tool for connecting to the server.
2.2. Creating a database
To practice this tutorial I will be using the learning database. You can below command to create a database.
Create database sql query
-- create database create database learning
2.3 Creating a Person table and inserting data
Create a person table in the learning database. You can use the below script to create a table, insert some data into it and view it.
Person sql script
-- drop table
drop table person;
-- create person table
create table person (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(50),
email VARCHAR(50),
date_of_birth DATE,
country_of_birth VARCHAR(50)
);
-- insert sample records in person table
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Had', 'Aronsohn', 'Male', 'haronsohn0@macromedia.com', '2016-01-03', 'Portugal');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Katya', 'Trainor', 'Female', 'ktrainor1@typepad.com', '1984-07-05', 'Canada');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Angelle', 'Conahy', 'Female', 'aconahy2@yandex.ru', '1992-02-24', 'Afghanistan');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Enrika', 'Osmond', 'Female', 'eosmond3@sun.com', '1984-03-11', 'Indonesia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Xever', 'Foldes', 'Male', 'xfoldes4@eepurl.com', '1977-06-22', 'Philippines');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Egbert', 'Badam', 'Male', 'ebadam5@list-manage.com', '1987-10-02', 'Colombia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Jaquenetta', 'Goodison', 'Female', 'jgoodison6@tripod.com', '1985-03-01', 'Costa Rica');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Ardene', 'Hunnywell', 'Female', 'ahunnywell7@howstuffworks.com', '2011-09-16', 'Philippines');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Ruperta', 'Druhan', 'Female', 'rdruhan8@taobao.com', '1971-11-27', 'Faroe Islands');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Niels', 'Lorenz', 'Male', 'nlorenz9@theguardian.com', '2012-07-10', 'Sweden');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Hamid', 'Mattek', 'Male', 'hmatteka@dedecms.com', '2010-10-06', 'Indonesia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Andras', 'Antognoni', 'Male', 'aantognonib@jalbum.net', '2011-05-16', 'Iran');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Hermia', 'Dymocke', 'Female', 'hdymockec@dion.ne.jp', '1977-11-11', 'Brazil');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Bendix', 'Towlson', 'Male', 'btowlsond@nytimes.com', '2020-05-09', 'Armenia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Minny', 'O''Corrin', 'Female', 'mocorrine@woothemes.com', '2021-09-07', 'China');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Brooks', 'MacDwyer', 'Male', 'bmacdwyerf@blogger.com', '2000-10-25', 'China');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Catha', 'Lavigne', 'Female', 'clavigneg@discovery.com', '1990-05-01', 'Portugal');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Shay', 'Charle', 'Male', 'scharleh@sfgate.com', '2001-09-11', 'Indonesia');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Lenee', 'Haskur', 'Female', 'lhaskuri@redcross.org', '1974-12-14', 'East Timor');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Fayette', 'Luker', 'Female', 'flukerj@squidoo.com', '1998-03-25', 'Sweden');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Don', 'Applewhite', 'Male', 'dapplewhitek@latimes.com', '1976-08-09', 'Iran');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Ric', 'Peggrem', 'Male', 'rpeggreml@domainmarket.com', '1993-01-28', 'Brazil');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Aprilette', 'Mattiazzi', 'Female', 'amattiazzim@nifty.com', '1994-10-10', 'Germany');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Lindy', 'Garvill', 'Male', 'lgarvilln@un.org', '1972-12-05', 'Brazil');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Francisco', 'Lebbern', 'Male', 'flebberno@accuweather.com', '1987-12-11', 'China');
insert into person (first_name, last_name, gender, email, date_of_birth, country_of_birth) values ('Elwood', 'Cumberbatch', 'Male', 'ecumberbatchp@businessweek.com', '2008-12-28', 'Czech Republic');
… other insert statements are omitted for brevity
-- fetch all records from the person table
select * from person;
Once the data is viewed in the person table it will be shown.
2.4 Understanding WHERE clause
Let us see some examples.
2.4.1 Fetch those records where the gender is female
The below sql query will fetch those records from the person table where gender is ‘Female’.
Sql query
-- fetch those records where gender is female select id, first_name, last_name, gender, email, country_of_birth from public.person where gender = 'Female';
If everything goes well the below result will be shown.
2.4.2 Count male population
The below sql query will count the male population in the person table.
Sql query
-- count male population count select count(id) as male_count from public.person where gender = 'Male';
If everything goes well the below result will be shown.
2.5 Understanding GROUP BY clause
Let us see some examples.
2.5.1 Count how many times a country name is used
The below sql query will count how many times a country name is used in the person table.
Sql query
-- count how many times a country name is used select country_of_birth, count(id) as times_used from public.person group by country_of_birth;
If everything goes well the below result will be shown.
2.5.2 Count Male and Female population
The below sql query will count the male and female populations in the person table.
Sql query
-- count male and female population select gender, count(id) as population_count from public.person group by gender;
If everything goes well the below result will be shown.
2.6 Understanding the HAVING clause
Let us see an example.
2.6.1 Count how many times a country name is used but the count should be greater than 1
The below sql query will count how many times a country name is used in the person table but filter those were the count > 1.
Sql query
-- count how many times a country name is used but the count should be greater than one select country_of_birth, count(id) from public.person group by country_of_birth having count(id) > 1;
If everything goes well the below result will be shown.
2.7 Understanding the ORDER BY clause
Let us see an example.
2.7.1 Sort records in ascending order
The below sql query will sort the records in ascending order of country name.
Sql query
-- fetch all records but sort them by country of birth in ascending order select id, first_name, last_name, gender, email, country_of_birth from public.person order by country_of_birth asc;
If everything goes well the below result will be shown.
3. Summary
In this tutorial, we learned the brief of sql, WHERE, GROUP BY, HAVING, and ORDER BY clauses and their practical implementation via different examples. You can download the sql scripts used in this tutorial from the Downloads section.
4. Download the Sql scripts
This was a tutorial to understand WHERE, GROUP BY, HAVING, and ORDER BY clauses in sql.
You can download the full source code of this example here: Where, Group by, Having, and Order by clause in SQL
