Q.Explain the concept of GROUP BY with help on an example.

Shape: Plain theory question calling for an explanation of the GROUP BY clause with a worked example.

---

The GROUP BY clause in SQL partitions rows of a table into groups based on the values in one or more columns. All rows that share the same value(s) in the grouping column(s) collapse into a single group. Aggregate functions—COUNT(), SUM(), AVG(), MAX(), MIN()—then operate on each group independently, producing one result row per group.

Without GROUP BY, an aggregate function processes the entire table as a single set. With it, you compute separate aggregates for each subset.

Syntax:

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

Every column in the SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function. Mixing ungrouped columns with aggregates is undefined—the DBMS does not know which row's value to pick for the ungrouped column within a group.

Example

Consider a table Sales:

Query: Find the total sales amount for each product.

SELECT Product, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Product;

How it works:

1. The GROUP BY Product clause partitions the five rows into three groups: all Laptop rows together, all Mouse rows together, all Keyboard rows together.
2. SUM(Amount) computes the sum within each group.
3. The result is one row per product.

Output:

Why GROUP BY exists

Imagine you have a table of sales transactions, each row recording one sale. You want to answer "What is the total revenue per product?" or "How many orders did each customer place?" These questions require you to partition your data into subsets—one subset per product, or one per customer—and then compute a summary statistic for each subset.

GROUP BY is the SQL clause that performs this partitioning. It takes all rows that share the same value in the grouping column(s) and treats them as a single logical unit. Any aggregate function (COUNT(), SUM(), AVG(), MAX(), MIN()) then operates within each group, producing one result per group rather than one result for the entire table.

Without GROUP BY, an aggregate function collapses the entire table into a single row. With GROUP BY, you get one row per distinct value (or combination of values) in the grouping column(s).

---

A concrete example

Suppose you manage a bookstore and have a Sales table:

Question: What is the total quantity sold in each category?

You want one row per category, showing the category name and the sum of Quantity for all books in that category.

SELECT Category, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY Category;

How it works:

1. GROUP BY Category partitions the six rows into three groups:

   • Programming: rows 1, 2, 5
   • History: rows 3, 6
   • Biography: row 4

2. SUM(Quantity) is evaluated separately for each group:

   • Programming: $2 + 1 + 2 = 5$
   • History: $3 + 1 = 4$
   • Biography: $1$

3. The result is one row per group:

---

Key rules

Every column in the SELECT list must be either:

• in the GROUP BY clause, or
• wrapped in an aggregate function.

This is because once you group, each result row represents multiple input rows. If you tried to SELECT BookTitle without grouping by it, SQL wouldn't know which of the three Programming book titles to show in the single Programming row—hence it's forbidden.

Valid:

SELECT Category, COUNT(*) AS NumOrders
FROM Sales
GROUP BY Category;

Invalid (will raise an error):

SELECT Category, BookTitle, COUNT(*)
FROM Sales
GROUP BY Category;
-- BookTitle is neither grouped nor aggregated

---

Grouping by multiple columns

You can group by more than one column. Each unique combination of values becomes a separate group.

Question: Total quantity sold per category and price point?

SELECT Category, Price, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY Category, Price;

Now rows are grouped by (Category, Price) pairs. For instance, if two Programming books have different prices, they form two separate groups.

---

Using HAVING to filter groups

WHERE filters rows before grouping. HAVING filters groups after aggregation.

Question: Which categories sold more than 3 items in total?