Screen Link:

https://app.dataquest.io/m/189/intermediate-joins-in-sql/7/generating-columns-with-the-case-statement

```
SELECT
c.first_name || " " || c.last_name customer_name,
COUNT(i.invoice_id) number_of_purchases,
SUM(i.total) total_spent,
CASE
WHEN sum(i.total) < 40 THEN 'small spender'
WHEN sum(i.total) > 100 THEN 'big spender'
ELSE 'regular'
END
AS customer_category
FROM invoice i
INNER JOIN customer c ON i.customer_id = c.customer_id
GROUP BY 1 ORDER BY 1;
```

In the solution, why do we sum the total? Doesn’t total already mean it is a sum, or are there multiple purchases and invoices?

2 Likes

Hello @vroomvroom,

`Total`

is a line on an `invoice`

. So every `receipt / invoice`

has `total`

for every transaction. However, a customer can buy more than once `(therefore, we can group by customers)`

. So this is why we have to `sum`

the `total`

for each customer.

1 Like

Hi @vroomvroom,

Each customer can purchases multiple times from us which means there can be multiple invoices for a single customer. Each invoice contains the total price for all the tracks purchased by that customer in a single transaction. Since each custumer may make multiple transactions (purchase multiple times), they could have more than one invoice, and as a result, more than one total price. We sum the all the total in all the invoices belonging to a single customer has to find out how much that customer has spent all together so we can figure out kind of spender they are.

Thank you @adewalade and @monorienaghogho, this makes sense now.

2 Likes