MySQL Forums
Forum List  »  Newbie

Re: Classifying Northwind customers according to how much they spent
Posted by: Peter Brawley
Date: December 21, 2021 02:52PM

Well done on #1 & #2.

A custom function could work but it'd be slower. You're one CASE expression away, #3 needs what's called a "pivot table" on your grouped values, for a review of the basics see "Pivot table basics: rows to columns" at https://www.artfulsoftware.com/infotree/queries.php, in you problem something like ...

WITH orderTotals AS (
  SELECT 
    orders.CustomerID,
    SUM(orderdetails.Quantity * orderdetails.UnitPrice) AS total,
    YEAR(orders.OrderDate) AS orderYear
  FROM orders
  JOIN orderdetails ON orders.OrderID = orderdetails.OrderID
  GROUP BY orders.CustomerID,orderYear
)
SELECT 
  customers.CustomerID,
  customers.CompanyName,
  orderTotals.total,        -- CORRECTION: ADD MISSING COMMA
  CASE                      -- PIVOT TABLE
    WHEN total<1000 THEN 'Low-value' 
    WHEN total<5000 THEN 'Medium-value'
    WHEN total<10000 THEN 'High-value'
    ELSE 'Very-high-value'
  END AS Category
FROM customers
LEFT JOIN orderTotals ON customers.CustomerID=orderTotals.CustomerID
WHERE orderTotals.orderYear=2016
GROUP BY CustomerID
ORDER BY customerID;



Edited 2 time(s). Last edit at 12/21/2021 04:40PM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: Classifying Northwind customers according to how much they spent
December 21, 2021 02:52PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.