CODEWITHSUNDEEP
sqlsql-servergroup-byrow-number
Ahmet Altun
Ahmet Altun

Reputation: 4039

SQL Server Group By Complex Query

In SQL Server, suppose we have a SALES_HISTORY table as below.

CustomerNo  PurchaseDate    ProductId
    1         20120411         12
    1         20120330         13
    2         20120312         14
    3         20120222         16
    3         20120109         16

... and many records for each purchase of each customer...

How can I write the appropriate query for finding:

For each customer,

The result table must have columns like: 

CustomerNo, 
MostPurchasedProductId, 
MostPurchasedProductPercentage

Upvotes: 0

Views: 1283

Answers (2)

Lamak
Lamak

Reputation: 70638

Assuming SQL Server 2005+, you can do the following:

;WITH CTE AS
(
    SELECT *, 
           COUNT(*) OVER(PARTITION BY CustomerNo, ProductId) TotalProduct,
           COUNT(*) OVER(PARTITION BY CustomerNo) Total
    FROM YourTable
), CTE2 AS
(
    SELECT *,
           RN = ROW_NUMBER() OVER(PARTITION BY CustomerNo 
                                  ORDER BY TotalProduct DESC)
    FROM CTE
)
SELECT CustomerNo, 
       ProductId MostPurchasedProductId, 
       CAST(TotalProduct AS NUMERIC(16,2))/Total*100 MostPurchasedProductPercent
FROM CTE2
WHERE RN = 1

You still need to deal when you have more than one product as the most purchased one. Here is a sqlfiddle with a demo for you to try.

Upvotes: 1

Cyril Gandon
Cyril Gandon

Reputation: 17048

Could do a lot prettier, but it works:

with cte   as(
select CustomerNo, ProductId, count(1) as c
from SALES_HISTORY
group by CustomerNo, ProductId)

select CustomerNo, ProductId as MostPurchasedProductId, (t.c * 1.0)/(select sum(c) from cte t2 where t.CustomerNo = t2.CustomerNo) as MostPurchasedProductPercentage
from cte t
where c = (select max(c) from cte t2 where t.CustomerNo = t2.CustomerNo)

SQL Fiddle

Upvotes: 0

Related Questions