Re: Classifying Northwind customers according to how much they spent

[Peter Brawley]

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.