SQL Clauses - How to Use GroupBy, OrderBy & Having Clauses in SQL?

Unleash the potential of SQL Clauses – GroupBy, OrderBy, and Having! Check out this recipe to optimize your SQL queries & streamline database operations.

Recipe Objective - SQL Clauses - How to Use GroupBy, OrderBy & Having Clauses in SQL? 

When it comes to managing and manipulating data in relational databases, SQL (Structured Query Language) plays a pivotal role. SQL offers a variety of clauses to filter, sort, and aggregate data, providing powerful tools for extracting meaningful insights. Check out this recipe guide to delve into three essential SQL clauses: GroupBy, OrderBy, and Having. Let's explore each clause's functionality, use cases, and syntax.

What is GroupBy Clause in SQL Server? 

The GroupBy clause is used to arrange identical data into groups based on one or more columns. It is particularly useful when you want to perform aggregate functions (like SUM, AVG, COUNT) on specific groups within a dataset.

Syntax of GroupBy Clause 

SELECT column1, column2, aggregate_function(column3)

FROM table

GROUP BY column1, column2;

Why is GroupBy Used in SQL? 

The GroupBy clause is employed to condense large datasets into more manageable and meaningful summaries. It allows you to gain insights into patterns, relationships, and trends within your data.

How to Use GroupBy Clauses in SQL?

The Group By statement in SQL is used to group related data. To combine rows together by common column values, the GROUP BY clause is used in conjunction with the SELECT statement and aggregate functions.

Syntax:

SELECT column_name1, column_name2,...

FROM table_name

WHERE condition

GROUP BY column_name(s);

Code:
SELECT COUNT(customer_name), country

FROM customers

GROUP BY country;

SQL GROUP BY Clause 

Output:

+----------------------+---------+

| COUNT(customer_name) | country |

+----------------------+---------+

|                    3 | England |

|                    1 | Ireland |

|                    1 | USA     |

+----------------------+---------+

2 rows in set (0.00 sec)

What is OrderBy Clause in SQL? 

The OrderBy clause is used to sort the result set of a query in ascending or descending order based on one or more columns. This ensures that the retrieved data is presented in a structured and easily interpretable manner.

Syntax of OrderBy Clause

SELECT column1, column2

FROM table

ORDER BY column1 [ASC | DESC], column2 [ASC | DESC];

OrderBy Clause in SQL Example

SELECT product_name, price

FROM products

ORDER BY price DESC;

Order By Where Clause in SQL

You can combine the OrderBy and Where clauses to filter and sort data simultaneously.

SELECT product_name, price

FROM products

WHERE category = 'Electronics'

ORDER BY price DESC;

How to use Order By clause in SQL? 

The Order By statement in SQL is used to retrieve data in an increasing or decreasing order. Here’s how you can use the order by statement –

Let us retrieve data from the customers table ordering by income column in descending order.

Code:

SELECT customer_id, customer_name

FROM customers
ORDER BY income DESC;  

SQL ORDER BY Clause

Output:

+-------------+----------------+

| customer_id | customer_name  |

+-------------+----------------+

|         101 | Thomas Shelby  |

|         103 | Alfie Solomons |

|         105 | May Carleton   |

|         104 | Michael Gray   |

|         102 | Grace Burgess  |

+-------------+----------------+

5 rows in set (0.00 sec)

Let us retrieve data from the customers table ordering by income column in ascending order.

Code:

SELECT customer_id, customer_name, income

FROM customers

ORDER BY income ASC;

GROUP BY Clause in SQL Example

Output:

+-------------+----------------+----------+

| customer_id | customer_name  | income   |

+-------------+----------------+----------+

|         102 | Grace Burgess  | 10000000 |

|         104 | Michael Gray   | 56000000 |

|         103 | Alfie Solomons | 78000000 |

|         105 | May Carleton   | 78000000 |

|         101 | Thomas Shelby  | 90000000 |

+-------------+----------------+----------+

5 rows in set (0.00 sec)

What is the Having Clause in SQL?

The Having clause is used in combination with the GroupBy clause to filter the results of aggregate functions based on specified conditions. It is applied after the data has been grouped.

Syntax of Having Clause

SELECT column1, aggregate_function(column2)

FROM table

GROUP BY column1

HAVING aggregate_function(column2) condition;

Having Clause in SQL Example

SELECT department, AVG(salary) as avg_salary

FROM employees

GROUP BY department

HAVING AVG(salary) > 50000;

Why Is Having Clause Used in SQL? 

The Having clause is crucial for filtering grouped data based on aggregate function results. It allows you to extract only the groups that meet specific criteria.

How to use Having clauses in SQL?

The HAVING clause is most commonly used in conjunction with the GROUP BY clause to specify a filter condition for a group or aggregate. Only the SELECT statement can employ the HAVING clause.

Code:

SELECT COUNT(customer_name), country

FROM customers

GROUP BY country

HAVING COUNT(customer_name)>1;

HAVING Clause in SQL Example

Output:

+----------------------+---------+

| COUNT(customer_name) | country |

+----------------------+---------+

|                    3 | England |

+----------------------+---------+

1 row in set (0.00 sec)

Explore More About SQL Clauses with ProjectPro! 

SQL clauses such as GroupBy, OrderBy, and Having are essential for effective database management and querying. The practical application of these clauses becomes evident when working on real-world projects, allowing you to enhance your skills and understanding of SQL. To delve deeper into the world of SQL clauses and gain valuable hands-on experience, consider exploring more through ProjectPro. With its extensive repository of over 270+ projects focused on data science and big data, ProjectPro provides a comprehensive platform to sharpen your SQL skills and excel in the realm of database management. 

What Users are saying..

profile image

Ed Godalle

Director Data Analytics at EY / EY Tech
linkedin profile url

I am the Director of Data Analytics with over 10+ years of IT experience. I have a background in SQL, Python, and Big Data working with Accenture, IBM, and Infosys. I am looking to enhance my skills... Read More

Relevant Projects

Build a Customer Churn Prediction Model using Decision Trees
Develop a customer churn prediction model using decision tree machine learning algorithms and data science on streaming service data.

Time Series Forecasting with LSTM Neural Network Python
Deep Learning Project- Learn to apply deep learning paradigm to forecast univariate time series data.

MLOps Project to Build Search Relevancy Algorithm with SBERT
In this MLOps SBERT project you will learn to build and deploy an accurate and scalable search algorithm on AWS using SBERT and ANNOY to enhance search relevancy in news articles.

Azure Text Analytics for Medical Search Engine Deployment
Microsoft Azure Project - Use Azure text analytics cognitive service to deploy a machine learning model into Azure Databricks

Build CI/CD Pipeline for Machine Learning Projects using Jenkins
In this project, you will learn how to create a CI/CD pipeline for a search engine application using Jenkins.

Recommender System Machine Learning Project for Beginners-4
Collaborative Filtering Recommender System Project - Comparison of different model based and memory based methods to build recommendation system using collaborative filtering.

Recommender System Machine Learning Project for Beginners-1
Recommender System Machine Learning Project for Beginners - Learn how to design, implement and train a rule-based recommender system in Python

Hands-On Approach to Regression Discontinuity Design Python
In this machine learning project, you will learn to implement Regression Discontinuity Design Example in Python to determine the effect of age on Mortality Rate in Python.

Build a Multi Class Image Classification Model Python using CNN
This project explains How to build a Sequential Model that can perform Multi Class Image Classification in Python using CNN

Build an optimal End-to-End MLOps Pipeline and Deploy on GCP
Learn how to build and deploy an end-to-end optimal MLOps Pipeline for Loan Eligibility Prediction Model in Python on GCP