Using the SALE table of the CARSHOWROOM database along with GROUP BY and the HAVING clause:
(a) Display the number of cars purchased by each customer from the SALE table.
(b) Display the customer Id and number of cars purchased, only if the customer purchased more than 1 car.
(c)

Question

Using the SALE table of the CARSHOWROOM database along with GROUP BY and the HAVING clause:
(a) Display the number of cars purchased by each customer from the SALE table.
(b) Display the customer Id and number of cars purchased, only if the customer purchased more than 1 car.
(c) Display the number of people in each category of payment mode from the SALE table.
(d) Display the PaymentMode and the number of payments made using that mode, only for modes used more than once.

Accepted Answer

> [!TLDR]
> `GROUP BY` collapses the 6-row SALE table into per-customer / per-payment-mode groups; `HAVING` then filters which GROUPS survive (unlike `WHERE`, which filters rows before grouping).

This is a **code/query task**. All four parts work on the same SALE table:

| InvoiceNo | CarId | CustId | SaleDate | PaymentMode | EmpID | SalePrice |
|---|---|---|---|---|---|---|
| I00001 | D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613247.00 |
| I00002 | S001 | C0002 | 2018-12-12 | Online | E001 | 590321.00 |
| I00003 | S002 | C0004 | 2019-01-25 | Cheque | E010 | 604000.00 |
| I00004 | D002 | C0001 | 2018-10-15 | Bank Finance | E007 | 659982.00 |
| I00005 | E001 | C0003 | 2018-12-20 | Credit Card | E002 | 369310.00 |
| I00006 | S002 | C0002 | 2019-01-30 | Bank Finance | E007 | 620214.00 |

**(a)** Grouping by `CustID` and counting rows per group gives one row per customer:

```sql
SELECT CustID, COUNT(*) "Number of Cars" FROM SALE GROUP BY CustID;
```

C0001 appears in rows I00001 and I00004 → 2 cars; C0002 in I00002 and I00006 → 2 cars; C0003 and C0004 each appear once.

**(b)** Adding `HAVING COUNT(*)>1` keeps only the groups whose count exceeds 1 — C0001 and C0002:

```sql
SELECT CustID, COUNT(*) FROM SALE GROUP BY CustID HAVING COUNT(*)>1;
```

> [!WARNING]
> You cannot write this filter as `WHERE COUNT(*)>1` — `WHERE` runs before grouping and cannot see an aggregate's result. The aggregate condition must go in `HAVING`, which runs after the groups are formed.

**(c)** The same idea, grouped by `PaymentMode` instead, with `ORDER BY` to sort the output alphabetically:

```sql
SELECT PaymentMode, COUNT(PaymentMode) FROM SALE GROUP BY PaymentMode ORDER BY PaymentMode;
``` …

Q.Using the SALE table of the CARSHOWROOM database along with GROUP BY and the HAVING clause:

Concept understanding — SQL Aggregate Functions

(a) Number of cars purchased by each customer

(b) Only customers who purchased more than 1 car

(c) Number of people in each payment-mode category

(d) Payment modes used more than once

InvoiceNo	CarId	CustId	SaleDate	PaymentMode	EmpID	SalePrice
I00001	D001	C0001	2019-01-24	Credit Card	E004	613247.00
I00002	S001	C0002	2018-12-12	Online	E001	590321.00
I00003	S002	C0004	2019-01-25	Cheque	E010	604000.00
I00004	D002	C0001	2018-10-15	Bank Finance	E007	659982.00
I00005	E001	C0003	2018-12-20	Credit Card	E002	369310.00
I00006	S002	C0002	2019-01-30	Bank Finance	E007	620214.00

Q.Using the SALE table of the CARSHOWROOM database along with GROUP BY and the HAVING clause:

🎓Unlock everything free for 14 days

Unlock everything free for 14 days