GROUP BY single column in SQL

Introduction

GROUP BY helps us organize data by one column so we can see summaries for each group.

When you want to count how many items belong to each category.

When you want to find the total sales for each product.

When you want to see the average score for each student.

When you want to list unique values and some calculation for each.

When you want to group data by date, like sales per day.

Syntax

SQL

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

AGGREGATE_FUNCTION can be COUNT, SUM, AVG, MAX, MIN, etc.

Every column in SELECT that is not inside an aggregate must be in GROUP BY.

Examples

Counts how many employees are in each department.

SQL

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Adds up the prices of products in each category.

SQL

SELECT category, SUM(price)
FROM products
GROUP BY category;

Finds the average age of customers in each city.

SQL

SELECT city, AVG(age)
FROM customers
GROUP BY city;

Sample Program

This creates a sales table, adds some sales data, then sums amounts for each product.

SQL

CREATE TABLE sales (
  id INT,
  product VARCHAR(20),
  amount INT
);

INSERT INTO sales VALUES
(1, 'Apple', 10),
(2, 'Banana', 5),
(3, 'Apple', 15),
(4, 'Banana', 7),
(5, 'Cherry', 20);

SELECT product, SUM(amount) AS total_amount
FROM sales
GROUP BY product;

Important Notes

Every column in SELECT that is not inside an aggregate function must be in GROUP BY.

GROUP BY groups rows that have the same value in the specified column.

You can use ORDER BY after GROUP BY to sort the grouped results.

Summary

GROUP BY groups data by one column to summarize it.

Use aggregate functions like COUNT, SUM, AVG with GROUP BY.

Every non-aggregate column in SELECT must be in GROUP BY.