CodeCapsule

SQL GROUP BY statement

by

Here is a clear, practical, and complete guide to the SQL GROUP BY statement, with examples, rules, and common pitfalls.

✅ What GROUP BY Does

GROUP BY groups rows that share the same values so you can apply aggregate functions such as:

  • COUNT()

  • SUM()

  • AVG()

  • MIN()

  • MAX()

It turns many rows into summary rows.

🔹 Basic Syntax

SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1;

📌 Basic Examples

1. Count employees per department

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

2. Total sales per customer

SELECT customer_id, SUM(amount) AS total_spent
FROM payments
GROUP BY customer_id;

3. Average score per class

SELECT class, AVG(score) AS avg_score
FROM results
GROUP BY class;

🔥 GROUP BY with Multiple Columns

SELECT country, city, COUNT(*) AS store_count
FROM stores
GROUP BY country, city;

This groups first by country, then city.

🔥 GROUP BY + HAVING (Filter Aggregated Results)

HAVING is used after aggregation.
WHERE filters rows before grouping.

Example: Departments with more than 10 employees

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

🔧 GROUP BY with ORDER BY

Sort aggregated results:

SELECT category, SUM(sales) AS total_sales
FROM products
GROUP BY category
ORDER BY total_sales DESC;

🔥 Real-World Examples

Monthly revenue

(PostgreSQL example using DATE_TRUNC)

SELECT DATE_TRUNC('month', created_at) AS month,
SUM(total_amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

Count logins per user

SELECT user_id, COUNT(*) AS login_count
FROM logins
GROUP BY user_id;

Maximum order per customer

SELECT customer_id, MAX(total) AS largest_order
FROM orders
GROUP BY customer_id;

🧠 Important Rules & Pitfalls

✔ 1. Every non-aggregated column MUST be in GROUP BY

❌ Invalid:

SELECT department, salary
FROM employees
GROUP BY department;

✔ Valid:

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

✔ 2. Aggregate functions ignore NULLs

COUNT(column) ignores NULL
SUM, AVG, MIN, MAX ignore NULL

✔ 3. Use HAVING for aggregated filters

WHERE → before grouping
HAVING → after grouping

✔ 4. GROUP BY with DISTINCT isn’t usually needed

This:

SELECT DISTINCT department
FROM employees;

Is equal to:

SELECT department
FROM employees
GROUP BY department;

🏎 Performance Tips

✔ Index columns used in GROUP BY
✔ Use GROUP BY on low-cardinality columns carefully
✔ Avoid grouping on expressions unless necessary
✔ In large datasets, consider materialized views

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *