SQL GROUP BY

Learn how to group rows with common values and perform calculations such as COUNT, SUM, AVG, MIN, and MAX using SQL GROUP BY.

What is the GROUP BY Clause?

The GROUP BY clause groups rows that have the same values in one or more columns. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().

Basic GROUP BY Syntax

The basic syntax places the column used for grouping after the GROUP BY keyword.

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
  sale_id INTEGER,
  product TEXT,
  category TEXT,
  amount INTEGER
);

INSERT INTO sales VALUES
  (1, 'Notebook', 'Stationery', 500),
  (2, 'Pen Set', 'Stationery', 300),
  (3, 'Desk Lamp', 'Electronics', 1200),
  (4, 'USB Cable', 'Electronics', 700),
  (5, 'Marker Set', 'Stationery', 450);

SELECT
  category,
  COUNT(*) AS total_items
FROM sales
GROUP BY category
ORDER BY category;

GROUP BY with COUNT()

The COUNT() function can be used with GROUP BY to count how many rows belong to each group.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  order_id INTEGER,
  customer TEXT,
  city TEXT
);

INSERT INTO orders VALUES
  (101, 'Aarav', 'Delhi'),
  (102, 'Meera', 'Mumbai'),
  (103, 'Kabir', 'Delhi'),
  (104, 'Riya', 'Pune'),
  (105, 'Tara', 'Mumbai'),
  (106, 'Dev', 'Delhi');

SELECT
  city,
  COUNT(*) AS order_count
FROM orders
GROUP BY city
ORDER BY order_count DESC;

GROUP BY with SUM()

Use SUM() to calculate the total value for each group.

DROP TABLE IF EXISTS payments;

CREATE TABLE payments (
  payment_id INTEGER,
  method TEXT,
  amount INTEGER
);

INSERT INTO payments VALUES
  (1, 'Card', 1200),
  (2, 'Cash', 500),
  (3, 'Card', 800),
  (4, 'UPI', 1500),
  (5, 'Cash', 700),
  (6, 'UPI', 900);

SELECT
  method,
  SUM(amount) AS total_amount
FROM payments
GROUP BY method
ORDER BY total_amount DESC;

GROUP BY with AVG()

The AVG() function calculates the average value within each group.

DROP TABLE IF EXISTS exam_results;

CREATE TABLE exam_results (
  student_id INTEGER,
  student_name TEXT,
  subject TEXT,
  marks INTEGER
);

INSERT INTO exam_results VALUES
  (1, 'Anaya', 'Math', 82),
  (2, 'Rohan', 'Math', 74),
  (3, 'Ishita', 'Science', 91),
  (4, 'Karan', 'Science', 79),
  (5, 'Mira', 'Math', 88),
  (6, 'Dev', 'Science', 85);

SELECT
  subject,
  ROUND(AVG(marks), 2) AS average_marks
FROM exam_results
GROUP BY subject
ORDER BY subject;

GROUP BY with MIN() and MAX()

MIN() returns the smallest value in each group, while MAX() returns the largest value.

DROP TABLE IF EXISTS temperatures;

CREATE TABLE temperatures (
  reading_id INTEGER,
  city TEXT,
  temperature INTEGER
);

INSERT INTO temperatures VALUES
  (1, 'Delhi', 34),
  (2, 'Delhi', 38),
  (3, 'Delhi', 31),
  (4, 'Pune', 29),
  (5, 'Pune', 33),
  (6, 'Pune', 27),
  (7, 'Jaipur', 36),
  (8, 'Jaipur', 40);

SELECT
  city,
  MIN(temperature) AS lowest_temperature,
  MAX(temperature) AS highest_temperature
FROM temperatures
GROUP BY city
ORDER BY city;

GROUP BY Multiple Columns

You can group rows using more than one column. SQL creates a separate group for each unique combination of the specified columns.

DROP TABLE IF EXISTS store_sales;

CREATE TABLE store_sales (
  sale_id INTEGER,
  store TEXT,
  category TEXT,
  amount INTEGER
);

INSERT INTO store_sales VALUES
  (1, 'Central', 'Books', 500),
  (2, 'Central', 'Books', 700),
  (3, 'Central', 'Games', 1200),
  (4, 'West', 'Books', 400),
  (5, 'West', 'Games', 900),
  (6, 'West', 'Books', 600);

SELECT
  store,
  category,
  SUM(amount) AS total_sales
FROM store_sales
GROUP BY store, category
ORDER BY store, category;

GROUP BY with WHERE

The WHERE clause filters individual rows before they are grouped.

DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions (
  transaction_id INTEGER,
  category TEXT,
  amount INTEGER
);

INSERT INTO transactions VALUES
  (1, 'Books', 450),
  (2, 'Books', 900),
  (3, 'Games', 1200),
  (4, 'Games', 600),
  (5, 'Books', 300),
  (6, 'Games', 1500);

SELECT
  category,
  SUM(amount) AS total_amount
FROM transactions
WHERE amount >= 600
GROUP BY category
ORDER BY category;

GROUP BY with HAVING

The HAVING clause filters groups after the GROUP BY operation. It is commonly used with aggregate functions.

DROP TABLE IF EXISTS department_sales;

CREATE TABLE department_sales (
  sale_id INTEGER,
  department TEXT,
  amount INTEGER
);

INSERT INTO department_sales VALUES
  (1, 'Books', 1200),
  (2, 'Books', 900),
  (3, 'Electronics', 2500),
  (4, 'Electronics', 1800),
  (5, 'Clothing', 700),
  (6, 'Clothing', 600);

SELECT
  department,
  SUM(amount) AS total_sales
FROM department_sales
GROUP BY department
HAVING SUM(amount) > 2000
ORDER BY total_sales DESC;

GROUP BY with COUNT and HAVING

You can use HAVING with COUNT() to return only groups containing a certain number of rows.

DROP TABLE IF EXISTS registrations;

CREATE TABLE registrations (
  registration_id INTEGER,
  course TEXT,
  student TEXT
);

INSERT INTO registrations VALUES
  (1, 'SQL', 'Aarav'),
  (2, 'SQL', 'Meera'),
  (3, 'SQL', 'Kabir'),
  (4, 'Python', 'Riya'),
  (5, 'Python', 'Dev'),
  (6, 'HTML', 'Tara');

SELECT
  course,
  COUNT(*) AS student_count
FROM registrations
GROUP BY course
HAVING COUNT(*) >= 2
ORDER BY student_count DESC;

GROUP BY with ORDER BY

After grouping the data, ORDER BY can be used to sort the resulting groups.

DROP TABLE IF EXISTS monthly_sales;

CREATE TABLE monthly_sales (
  sale_id INTEGER,
  month_name TEXT,
  amount INTEGER
);

INSERT INTO monthly_sales VALUES
  (1, 'January', 1500),
  (2, 'January', 2200),
  (3, 'February', 1800),
  (4, 'February', 900),
  (5, 'March', 2700),
  (6, 'March', 1300);

SELECT
  month_name,
  SUM(amount) AS total_sales
FROM monthly_sales
GROUP BY month_name
ORDER BY total_sales DESC;

GROUP BY with Multiple Aggregate Functions

Multiple aggregate functions can be used in the same GROUP BY query to calculate several statistics for every group.

DROP TABLE IF EXISTS employee_payments;

CREATE TABLE employee_payments (
  payment_id INTEGER,
  department TEXT,
  salary INTEGER
);

INSERT INTO employee_payments VALUES
  (1, 'Design', 42000),
  (2, 'Design', 48000),
  (3, 'Engineering', 65000),
  (4, 'Engineering', 72000),
  (5, 'Marketing', 45000),
  (6, 'Marketing', 52000);

SELECT
  department,
  COUNT(*) AS employee_count,
  SUM(salary) AS total_salary,
  ROUND(AVG(salary), 2) AS average_salary,
  MIN(salary) AS lowest_salary,
  MAX(salary) AS highest_salary
FROM employee_payments
GROUP BY department
ORDER BY department;

GROUP BY with DISTINCT Values

GROUP BY can also be used to produce one result for every unique value in a column.

DROP TABLE IF EXISTS visitors;

CREATE TABLE visitors (
  visitor_id INTEGER,
  country TEXT
);

INSERT INTO visitors VALUES
  (1, 'India'),
  (2, 'Japan'),
  (3, 'India'),
  (4, 'Canada'),
  (5, 'Japan'),
  (6, 'India');

SELECT
  country,
  COUNT(*) AS visitor_count
FROM visitors
GROUP BY country
ORDER BY country;

WHERE vs HAVING

Feature	WHERE	HAVING
Filters	Individual rows	Groups
Used before GROUP BY	Yes	No
Common use	Filter source rows	Filter aggregate results
Example	WHERE amount > 500	HAVING SUM(amount) > 2000

Common Aggregate Functions with GROUP BY

Function	Purpose	Example
COUNT()	Counts rows or values	COUNT(*)
SUM()	Calculates a total	SUM(amount)
AVG()	Calculates an average	AVG(price)
MIN()	Finds the smallest value	MIN(price)
MAX()	Finds the largest value	MAX(price)

Advantages of GROUP BY

Organizes rows into meaningful groups.
Works with COUNT(), SUM(), AVG(), MIN(), and MAX().
Makes it easy to summarize large datasets.
Can group data using one or multiple columns.
Can be combined with WHERE, HAVING, and ORDER BY.

Best Practices

Use GROUP BY when you need summarized results for groups.
Use WHERE to filter individual rows before grouping.
Use HAVING to filter groups after aggregation.
Use meaningful aliases for calculated columns.
Use ORDER BY to make grouped results easier to understand.
When creating tutorial examples, reset tables with DROP TABLE IF EXISTS so the code can be run repeatedly.

The GROUP BY clause is an essential SQL feature for summarizing data. Combined with aggregate functions, it allows you to calculate counts, totals, averages, minimums, and maximums for each group.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.