EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL GROUP BY WHERE

SQL GROUP BY WHERE

Updated March 10, 2023

SQL GROUP BY WHERE

Introduction to SQL GROUP BY WHERE

In SQL standard, a GROUP BY clause is used to group rows with identical values for a specified field together and prepare a summary row for these rows using an aggregate function. A WHERE clause is used to filter rows based on a specified condition. When we use GROUP BY clause in conjugation with WHERE clause, the WHERE clause filters the rows first based on the mentioned condition and then GROUP BY clause prepares a summary row only for the filtered rows. It becomes very useful when we want to GROUP only specific rows together. For instance, rows within some specified range of values, dates etc.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax and parameters of SQL GROUP BY WHERE

The basic syntax used for writing GROUP BY with WHERE clause is as follows:

SELECT column_name_1, aggregate_function(column_name_2)
FROM table_name
WHERE condition expression
GROUP BY column_name_1;

The parameters used in the above-mentioned syntax are as follows:

  • column_name_1: column or field name according to which the rows have to be grouped together.
  • column_name_2: column or field name which has to be summarized using an aggregate function.
  • table_name: database table from which the said columns will be fetched from.
  • condition expression: condition on the basis of which the WHERE clause will filter the rows.

Here we have used the minimum possible clauses and commands; we can use JOINS, HAVING BY, ORDER BY etc., based on the requirement.

Examples of SQL GROUP BY WHERE

Given below are the examples of SQL GROUP BY WHERE:

In order to illustrate the usage and functionality of the GROUP BY clause with a WHERE clause, let us first create a dummy table called “sales”. Here is the CREATE TABLE statement for the same.

Code:

CREATE TABLE sales (
order_id int,
salesman_name character varying(50),
product_id character varying(50),
sales_region character varying(50),
sales_date date
);

Output:

SQL GROUP BY WHERE 1

The table has been successfully created. Our next task is to insert a few records in it to work with.

Here is the insert statement for the same.

Code:

INSERT INTO public.sales(
order_id, salesman_name, product_id, sales_region, sales_date)
VALUES (1,'Mohit K','Book11','New Delhi','2020-05-01'),
(2,'Rey Holt','Book11','Mumbai','2020-05-02'),
(3,'Swati Singh','Book24','New Delhi','2020-05-03'),
(4,'Indrani K','Book24','Mumbai','2020-05-01'),
(5,'Dave Prakash','Book11','Mumbai','2020-05-02'),
(6,'Joshua S','Book24','New Delhi','2020-05-03'),
(7,'Mrinali Pal','Book11','New Delhi','2020-05-04'),
(8,'Mohit K','Book24','New Delhi','2020-05-02'),
(9,'Rey Holt','Book24','Mumbai','2020-05-05'),
(10,'Indrani K','Book11','Mumbai','2020-05-04'),
(11,'Joshua S','Book24','New Delhi','2020-05-05'),
(12,'Mohit K','Book11','New Delhi','2020-05-04');

Output:

SQL GROUP BY WHERE 2

The command got executed successfully. Let’s check using a SELECT statement if the desired rows have been inserted.

Code:

SELECT * FROM sales;

Output:

SQL GROUP BY WHERE 3

Example #1

Find the total number of sales made by each salesman in the New Delhi region.

Code:

SELECT salesman_name, count(order_id)
FROM sales
WHERE sales_region = 'New Delhi'
GROUP BY salesman_name;

Output:

SQL GROUP BY WHERE 4

We can clearly observe from the result of the SELECT query that the WHERE clause has first filtered the rows having ‘New Delhi’, and then GROUP BY clause has grouped the filtered rows together.

For detailed information, you may refer to the image of the query plan below.

SQL GROUP BY WHERE 5

Example #2

Find the total number of sales made in each region between 1st May 2020 and 3rd May 2020.

Code:

SELECT sales_region, count(order_id)
FROM sales
WHERE sales_date BETWEEN '2020-05-01' AND '2020-05-03'
GROUP BY sales_region;

Output:

total number of sales made in each region

GROUP BY WHERE with HAVING clause:

Many beginner-level SQL developers find it hard to understand the difference between HAVING BY and WHERE clauses because they both serve the same purpose; that is, they filter records. A HAVING clause, when used in conjugation with the GROUP BY clause, filters grouped rows, whereas the WHERE clause filters records before grouping them. A HAVING clause is always used with GROUP BY, whereas WHERE can be used in any SELECT statement.

Example #3

Find the salesman who made more than 2 sales in the period from 1st May 2020 to 5th May 2020.

Code:

SELECT salesman_name
FROM sales
WHERE sales_date BETWEEN '2020-05-01' AND '2020-05-05'
GROUP BY salesman_name
HAVING count(order_id) > 2;

Output:

salesman who made more than 2 sales in the period

GROUP BY WHERE with ORDER BY clause:

Example #4

Find the product and the number of salesmen that sold the particular product in the Mumbai region.

Code:

SELECT product_id, count(salesman_name)
FROM sales
WHERE sales_region = 'Mumbai'
GROUP BY product_id
ORDER BY count(salesman_name) DESC;

Output:

product and the number of salesmen

Conclusion

In this article, we saw how to use the GROUP BY clause with the WHERE clause. A WHERE clause is usually used in conjugation with GROUP BY to prepare a summary row for only those rows that satisfy a particular condition.

Recommended Articles

We hope that this EDUCBA information on “SQL GROUP BY WHERE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Window Functions
  2. SQL UNION ALL
  3. SQL DECODE()
  4. SQL SELECT DISTINCT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more