Consider the SALE table from the CARSHOWROOM database:
a) Display the number of Cars purchased by each Customer from SALE table.
b) Display the Customer Id and number of cars purchased if the customer purchased more than 1 car from SALE table.
c) Display the number of people in e

Question

Consider the SALE table from the CARSHOWROOM database:
a) Display the number of Cars purchased by each Customer from SALE table.
b) Display the Customer Id and number of cars purchased if the customer purchased more than 1 car from SALE table.
c) Display the number of people in each category of payment mode from the table SALE.
d) Display the PaymentMode and number of payments made using that mode more than once.

Accepted Answer

> [!TLDR]
> Uses `GROUP BY` with `COUNT()`, and `HAVING` to filter the grouped results, run against the real `SALE` table (Table 9.11, CARSHOWROOM database) — not invented data.

SQL aggregate functions summarise a set of rows into one value. `GROUP BY` first collects rows into groups by a shared column value, then an aggregate like `COUNT(*)` runs once per group. `HAVING` filters *those groups* after aggregation — `WHERE` cannot do this, because `WHERE` filters individual rows *before* grouping.

## The real SALE table (Table 9.11)

| InvoiceNo | CarId | CustId | SaleDate | PaymentMode | EmpID | SalePrice |
|---|---|---|---|---|---|---|
| I00001 | D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613248.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) Number of cars purchased by each customer**

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

**Output**

| CustID | Number of Cars |
|---|---|
| C0001 | 2 |
| C0002 | 2 |
| C0003 | 1 |
| C0004 | 1 |

4 rows in set (0.00 sec)

C0001 bought 2 cars (I00001, I00004), C0002 bought 2 (I00002, I00006), C0003 and C0004 bought 1 each.

---

### **b) Customers who purchased more than 1 car**

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

**Output**

| CustID | COUNT(*) |
|---|---|
| C0001 | 2 |
| C0002 | 2 |

2 rows in set (0.30 sec)

`HAVING COUNT(*) > 1` keeps only the groups whose count exceeds 1 — C0003 and C0004 (count 1 each) are dropped.

---

### **c) Number of people in each category of payment mode**

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

**Output**

| PaymentMode | Cou

Q.Consider the SALE table from the CARSHOWROOM database:

Concept understanding — SQL Aggregate Functions

The real SALE table (Table 9.11)

The real SALE table (Table 9.11)

a) Number of cars purchased by each customer

b) Customers who purchased more than 1 car

c) Number of people in each category of payment mode

InvoiceNo	CarId	CustId	PaymentMode
I00001	D001	C0001	Credit Card
I00002	S001	C0002	Online
I00003	S002	C0004	Cheque
I00004	D002	C0001	Bank Finance
I00005	E001	C0003	Credit Card
I00006	S002	C0002	Bank Finance

Q.Consider the SALE table from the CARSHOWROOM database:

The real SALE table (Table 9.11)

a) Number of cars purchased by each customer

b) Customers who purchased more than 1 car

c) Number of people in each category of payment mode

🎓Unlock everything free for 14 days

Unlock everything free for 14 days