Sql Views, Grouping By Most Sold Items And Customers Who Purchased Most

This is my table: Using this query, I am getting most sold items: SELECT [Purchased Item], SUM([Overall Quantity purchased] ) FROM ReportDraft GROUP BY [Purchased Item] ORDER BY

Solution 1:

I would use window functions and conditional aggregation:

SELECT [Purchased Item], sum(total) as total,
       MAX(CASE WHEN seqnum = 1 THEN Customer END) as customer,
       MAX(Total) as max_quantity
FROM (SELECT [Purchased Item], Customer, SUM([Overall Quantity purchased] ) as total,
             ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY SUM([Overall Quantity purchased]) DESC) as seqnum
      FROM ReportDraft 
      GROUP BY [Purchased Item], Customer
     ) rd 
GROUP BY [Purchased Item]
ORDER BY SUM([Overall Quantity purchased] );