EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW