Query for Top 3 of each Product

[mflancour]

Product Percent
asdf 36%
asdf 25%
asdf 15%
asdf 12%
asdf 10%
rewq 45%
rewq 25%
rewq 20%
rewq 10%

IN the above data, I want only the top 3 for each Product and the tolal of the percent for those 3, so the results should be:
asdf 76%
rewq 90%

 

[r937]

select Product
     , sum(Percent) as TotalPercent
  from (
       select t1.Product
            , t1.Percent
         from daTable as t1
       inner
         join daTable as t2
           on t1.Product = t2.Product
          and t1.Percent <= t2.Percent
       group
           by t1.Product
            , t1.Percent
       having count(*) <= 3
       ) as data
group
    by Product

r937.com | rudy.ca

 

[PHV]

Another way:
SELECT Product, Sum(Percent) AS SumOfTop3Percent
FROM (SELECT Product, Percent
FROM yourTable AS A
WHERE Percent In (SELECT TOP 3 Percent
FROM yourTable WHERE Product=A.Product ORDER BY 1 DESC)
) AS T
GROUP BY Product

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

PHV, with or without ties?

r937.com | rudy.ca

 

[PHV]

Good catch Rudy !
The ties are problematic with the TOP predicate ...

 

[r937]

well, to set the record straight, mine is with ties ...

omg

i just realized it doesn't work with ties!

here, please let me fix it --

       select t1.Product
            , t1.Percent
         from daTable as t1
       inner
         join daTable as t2
           on t1.Product = t2.Product
          and t1.Percent < t2.Percent
       group
           by t1.Product
            , t1.Percent
       having count(*) < 3

r937.com | rudy.ca

 

[mflancour]

ok...thought i'd be able to convert this to my needs but didn't work out...my table is slightly more complex
CustName ProdId WhseID Percent

same idea as before, just with more fields...I need top 3 of each record (each record is unique per custname, prodid, whseid) with total for percent.

Basically if a product has 3 or less customers who make up 75% or more I want to see their info.

 

[r937]

select CustName
     , ProdId
     , WhseID
     , sum(Percent) as TotalPercent
  from (
       select t1.CustName
            , t1.ProdId
            , t1.WhseID
            , t1.Percent
         from Customers as t1
       inner
         join Customers as t2
           on t1.CustName = t2.CustName
          and t1.ProdId   = t2.ProdId
          and t1.WhseID   = t2.WhseID
          and t1.Percent  < t2.Percent
       group
           by t1.CustName
            , t1.ProdId
            , t1.WhseID
            , t1.Percent
       having count(*) < 3
       ) as data
group
    by CustName
     , ProdId
     , WhseID
having sum(Percent) >= 75

r937.com | rudy.ca

 

[PHV]

SELECT ProdId, Sum(Percent) AS SumOfTop3Percent
FROM (SELECT ProdId, Percent
FROM yourTable AS A
WHERE Percent In (SELECT TOP 3 Percent
FROM yourTable WHERE ProdId=A.ProdId ORDER BY 1 DESC)
) AS T
GROUP BY ProdId
HAVING Sum(Percent)>0.75

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

phv, top 3 percent????

r937.com | rudy.ca

 

[mflancour]

Not sure what I am doing wrong, but I am only getting about 10 results when I should be getting several hunderd, and of the ten or so returned the total percent is not correct.

SELECT data.CustName, data.ProdId, Sum(data.[sum%]) AS TotalPercent
FROM (select t1.CustName
, t1.ProdId
, t1.WhseID
, t1.[sum%]
from [qry_13-1_ForecastCustomStock] as t1
inner
join [qry_13-1_ForecastCustomStock] as t2
on t1.CustName = t2.CustName
and t1.ProdId = t2.ProdId
and t1.WhseID = t2.WhseID
and t1.[sum%] < t2.[sum%]
group
by t1.CustName
, t1.ProdId
, t1.WhseID
, t1.[sum%]
having count(*) < 3
) AS data
GROUP BY data.CustName, data.ProdId
HAVING (((Sum(data.[sum%]))>=0.75));

 

[PHV]

Have you tried this ?
SELECT ProdId, Sum([sum%]) AS SumOfTop3Percent
FROM (SELECT ProdId, [sum%]
FROM [qry_13-1_ForecastCustomStock] AS A
WHERE [sum%] In (SELECT TOP 3 [sum%]
FROM [qry_13-1_ForecastCustomStock] WHERE ProdId=A.ProdId ORDER BY 1 DESC)
) AS T
GROUP BY ProdId
HAVING Sum([sum%])>=0.75

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

my example use HAVING > 75

percents go from 0 to 100

what kind of values are stored in the "percent" column?

r937.com | rudy.ca

 

[mflancour]

it's only formated as a percent the actual values are decimals less than 1 but greater than 0

 

[mflancour]

Tried this, but the query just hangs and never gives me data.

SELECT ProdId, CustName, Sum([sum%]) AS SumOfTop3Percent
FROM [SELECT ProdId, CustName, [sum%]
FROM [qry_13-1_ForecastCustomStock] AS A
WHERE [sum%] In (SELECT TOP 3 [sum%]
FROM [qry_13-1_ForecastCustomStock] WHERE ProdId=A.ProdId and CustName= A.CustName ORDER BY 1 DESC)
]. AS T
GROUP BY ProdId, CustName
HAVING Sum([sum%])>=0.75;

 

[mflancour]

Hey, thanks for all the help so far....I'm still unable to get this stupid query working though...any thoughts with the sql I have for either methog above as to what I should change?

 

[PHV]

what is the SQL code of qry_13-1_ForecastCustomStock ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mflancour]

SELECT [qry_13-2_ForecastCustomStock].CustName, [qry_13-2_ForecastCustomStock].ProdId, [qry_13-2_ForecastCustomStock].WhseId, Sum([qry_13-2_ForecastCustomStock].Sales) AS SumSales, Sum([qry_13-2_ForecastCustomStock].Quantity) AS SumQuantity, Avg([qry_13-2_ForecastCustomStock].[%]) AS [Sum%]
FROM [qry_13-2_ForecastCustomStock]
GROUP BY [qry_13-2_ForecastCustomStock].CustName, [qry_13-2_ForecastCustomStock].ProdId, [qry_13-2_ForecastCustomStock].WhseId, [qry_13-2_ForecastCustomStock].Cono, [qry_13-2_ForecastCustomStock].spclnonstcktype
HAVING ((([qry_13-2_ForecastCustomStock].Cono)=1) AND (([qry_13-2_ForecastCustomStock].spclnonstcktype)<>"n"));

 

[mflancour]

SELECT dbo_v_CustomerPercentofQtySales.*
FROM dbo_v_CustomerPercentofQtySales
WHERE (((dbo_v_CustomerPercentofQtySales.[%])<1 And (dbo_v_CustomerPercentofQtySales.[%])>0));