Computer Science · Ch 9 — Structured Query Language (SQL)
GROUP BY Clause in SQL
GROUP BY Clause in SQL
The GROUP BY clause is used when you need to work with groups of rows that share the same value in a particular column, rather than with individual rows. For example, you might want to know how many cars each employee sold, or the total sale amount for each car model. The GROUP BY clause collects all rows that have identical values in the specified column into a single group, and then aggregate functions like COUNT, MAX, MIN, AVG, and SUM can be applied to each group.
The HAVING clause is used alongside GROUP BY to place conditions on the groups themselves — it filters groups after they have been formed, much like WHERE filters individual rows before grouping.
The textbook uses the SALE table from the CARSHOWROOM database to demonstrate these ideas. The table has columns: InvoiceNo, CarId, CustId, SaleDate, PaymentMode, EmpID, SalePrice, and Commission. Columns like CarID, CustID, PaymentMode, and EmpID naturally contain repeated values, making them suitable for grouping.
The GROUP BY clause must appear after the FROM clause (and after any WHERE clause, if present) but before the ORDER BY clause.
Example 9.23(a) — Display the number of cars purchased by each customer from the SALE table.
SELECT CustID, COUNT(*) "Number of Cars"
FROM SALE
GROUP BY CustID;
The result shows that customer C0001 bought 2 cars, C0002 bought 2 cars, C0003 bought 1 car, and C0004 bought 1 car. The COUNT(*) counts the number of rows in each group — that is, the number of sales to each customer.
Example 9.23(b) — Display the customer ID and number of cars purchased, but only for customers who purchased more than one car.
SELECT CustID, COUNT(*)
FROM SALE
GROUP BY CustID
HAVING COUNT(*) > 1;
This time the output shows only C0001 and C0002, each with a count of 2. The HAVING clause filters out groups where the count is 1 or less. Notice that HAVING uses the aggregate function COUNT(*) in its condition — this is something WHERE cannot do, because WHERE operates on individual rows before grouping.
A common mistake is to use WHERE instead of HAVING to filter groups. Remember: WHERE filters rows before grouping; HAVING filters groups after grouping. You cannot use aggregate functions in a WHERE clause.
Example 9.23(c) — Display the number of people in each category of payment mode from the SALE table, ordered alphabetically by payment mode.
SELECT PaymentMode, COUNT(PaymentMode)
FROM SALE
GROUP BY Paymentmode
ORDER BY Paymentmode;
The output lists four payment modes: Bank Finance (2), Cheque (1), Credit Card (2), and Online (1). The ORDER BY clause sorts the result alphabetically by PaymentMode. Note that the column name in the GROUP BY clause is written as Paymentmode (case-insensitive in MySQL), and the SELECT clause uses PaymentMode — both refer to the same column.
Example 9.23(d) — Display the payment mode and the number of payments made using that mode, but only for modes that appear more than once, and order the result alphabetically.
SELECT PaymentMode, Count(PaymentMode)
FROM SALE
GROUP BY Paymentmode
HAVING COUNT(*) > 1
ORDER BY Paymentmode;
``` …