MySQL Forums
Forum List  »  Newbie

Re: Best way to get top 3 sales categories from a database
Posted by: Peter Brawley
Date: January 10, 2022 06:30PM

As described in the cited articles, eg ...

WITH
  subcategoryExpenditures AS (
    SELECT 
      CustomerID, 
      ProductSubCategory.Name as ProductSubCategoryName, 
      Round( SUM(UnitPrice),2) As subcategoryTotal
    FROM salesorderheader
    JOIN salesorderdetail USING(SalesOrderID)
    JOIN product USING(ProductID)
    JOIN productsubcategory USING(ProductSubcategoryID)
    GROUP BY CustomerID,ProductSubcategoryID
  ),
 expenditureRanks AS (
    SELECT 
      CustomerID, ProductSubcategoryname, subcategoryTotal,
      RANK() OVER ( partition by CustomerID 
                    order by subcategoryTotal desc 
                  ) AS subcategoryRank
    FROM subcategoryExpenditures
  )
SELECT 
  customerID, 
  GROUP_CONCAT( if(subcategoryrank=1,productsubcategoryname,null) ) AS '1st',
  GROUP_CONCAT( if(subcategoryrank=2,productsubcategoryname,null) ) AS '2nd',
  GROUP_CONCAT( if(subcategoryrank=3,productsubcategoryname,null) ) AS '3rd'
FROM expenditureRanks
WHERE CustomerID IN (13763,13836,20331,21113,26313)
GROUP BY CustomerID
ORDER BY CustomerID ;

Options: ReplyQuote


Subject
Written By
Posted
Re: Best way to get top 3 sales categories from a database
January 10, 2022 06:30PM


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.