# Summing the Total

My Code:

``````SELECT c.first_name ||' '|| c.last_name customer_name,
SUM(iil.quantity) number_of_purchases,
SUM(iil.unit_price * iil.quantity) total_spent,
CASE
WHEN SUM(iil.quantity) < 40 THEN 'small_spender'
WHEN SUM(iil.quantity) > 100 THEN 'big spender'
WHEN SUM(iil.quantity) > 40 & SUM(iil.quantity) < 100 THEN 'regular'
END
AS customer_category
FROM customer c
LEFT JOIN (    SELECT il.*, i.customer_id, i.total, i.invoice_id
FROM invoice i
LEFT JOIN invoice_line il ON il.invoice_id = i.invoice_id) iil
ON iil.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY customer_name
``````

What I expected to happen:
I expected the output to be the accepted answer on Dataquest but it is not.

What actually happened:

``````Instead, it tells me the answer doesn't look right
``````

When I input the Dataquest answer, however, that answer doesnât make sense to me. The total_spent is too large when you consider the number of purchases. Could someone please explain where I am going wrong?

Your solution looks good except for that tricky `number_of_purchases` and a slight difference in `total_spent` due to rounding errors. I wouldnât worry too much about the rounding errors but we should take a deeper look at why your `number_of_purchases` is so different (much higher) than the solutionâs.

``````SUM(iil.quantity) number_of_purchases
``````

Solution code:

``````COUNT(i.invoice_id) number_of_purchases
``````

Comparing the two, we see that the difference is: ânumber of tracks purchasedâ vs ânumber of times a customer made a purchase.â We want the second one. Keep in mind, itâs possible to purchase more than one track at a time.

I also spotted another small problem with your code with respect to `customer_category`. We want to label customers (`small_spender`, `regular`, and `big_spender`) based on how much the spend, not on how many tracks they purchased. I noticed this because `Diego GutiĂ©rrez` is labelled as `regular` by your code but he should be `small_spender` because his `total_spent` is under \$40.

Let me know if this helps or if I can clarify anything else for you!

Thank you so much! I have fixed my code. I also realised I was needlessly complicating the task because I assumed they were asking for ânumber of tracks purchasedâ.

Nicely done, congrats! Just know, this is very common with SQL: making it more complicated than it needs to be! Itâs all a learning process. Just keep going and experiment with the code until it makes sense.

Happy coding!

1 Like