Why Sum the Total?

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