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, in you problem something like ...

WITH orderTotals AS (
    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
  customers.CompanyName,,        -- 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
ORDER BY customerID;

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

Options: ReplyQuote

Written By
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.