CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
vovkjn
vovkjn

Reputation: 99

select top selling size by product group from table

I have table called Orders. There are three columns: CustomerID, ProductGroup, Size.

How can I get TOP selling size by ProductGroup from this table?

I can do it 1 by 1 with 

SELECT TOP 1 Count(customerid) as customers, ProductGroup, Size 
FROM Orders 
WHERE ProductGroup = xxx 
GROUP BY ProductGroup, Size
ORDER BY Count(customerid) DESC

However, I would like to get full list at once.

Upvotes: 0

Views: 374

Answers (5)

Tirthak Shah
Tirthak Shah

Reputation: 515

Not sure, but it may help you.

Declare @temp table(CustomerID int, ProductGroup varchar(10), Size int)

insert into @temp
Select 1,'ABC',15 union all
Select 2,'ABC',10 union all
Select 3,'XYZ',12 union all
Select 4,'ABC',15 union all
Select 3,'XYZ',12 union all
Select 3,'XYZ',12 union all
Select 3,'XYZ',15 union all
Select 3,'XYZ',11 union all
Select 3,'XYZ',12 

Select * from (
Select RANK() Over(PARTITION BY ProductGroup order by customers desc) RankVal,customers,ProductGroup, Size 
from (
SELECT Count(CustomerID) as customers, ProductGroup, Size 
FROM  @temp 
GROUP BY ProductGroup, Size
) T
)T1
WHERE RankVal = 1

Upvotes: 2

kiks73
kiks73

Reputation: 3758

You can try to use the MAX operator and put your SQL in a subquery, like this (not tested):

  SELECT MAX(customers), ProductGroup, Size FROM Orders GROUP BY ProductGroup, Size HAVING MAX(customers) = 
    SELECT MAX(customers) AS myMax FROM (
        SELECT Count(customerid) as customers, ProductGroup, Size
        FROM Orders 
        GROUP BY ProductGroup, Size) AS O
    GROUP BY ProductGroup, Size

Upvotes: 0

Eralper
Eralper

Reputation: 6612

Please check following SELECT query with SQL Count using Partition By clause

;with cte as (
    SELECT 
        Distinct ProductGroup, Size, 
        COUNT(*) OVER (Partition By ProductGroup, Size) cnt
    FROM Customers
)
select 
    ProductGroup, Size, cnt
from (
    select *, 
        rn = ROW_NUMBER() OVER (Partition By ProductGroup Order By cnt desc)
    from cte
) t 
where rn = 1

Upvotes: 1

john igneel
john igneel

Reputation: 407

You can use the aggregate function max() to select the highest size and group it according to ProductGroup.

SELECT COUNT(customerid) as customers, ProductGroup, MAX(Size) FROM 
    Orders WHERE Size IN (SELECT MAX(Size) FROM Orders) GROUP BY ProductGroup
    ORDER BY customerid DESC;

Note that this query has not been tested yet and I wonder if why do you need to get the count of customerid if your selecting only the highest Size per ProductGroup.

Upvotes: 0

contradictioned
contradictioned

Reputation: 1253

You want to have the top selling product for every ProductGroup. The way to achieve this, is to use a group by, e.g. via

SELECT ProductGroup, MAX(size)
FROM orders
GROUP BY ProductGroup

This way you get a result table with one column per ProductGroup and the maximum of the sizes by this product group.

Upvotes: 0

Related Questions