ChrisM@home (3/23/2014)

This query works only because of the sequence of RepairId in table2, which already orders most-used tyres ahead of least-used tyres, probably by accident. If table2 really is artificially ordered in this way, you're ok, otherwise you will get incorrect results. Here's a copy of Lyn's dataset slightly adjusted to show this:

DROP TABLE dbo.Table1

CREATE TABLE dbo.Table1 (DealerId INT, RepairId INT);

INSERT INTO dbo.Table1 VALUES

(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),

(1,8),(1,9),(1,10),(2,11),(2,12),(2,13),

(3,14),(3,15),(3,16);

DROP TABLE dbo.Table2

CREATE TABLE dbo.Table2 (RepairId INT, tire VARCHAR(15));

INSERT INTO dbo.Table2 VALUES (9,'225/65R17 102T'),

(7,'225/65R17 102T'),(3,'225/65R17 102T'),(4,'235/60R18 102V'),

(5,'235/60R18 102V'),(6,'235/60R18 102V'),(8,'235/60R18 102V'),

(2,'205/55R16 89H'),(1,'205/70R15 89H'),(13,'225/65R17 102T'),

(14,'235/60R18 102V'),(15,'235/60R18 102V'),(16,'235/60R18 102V');

and a query which works:

SELECT DealerID, tire, RepairsOfThisTire, TotalRepairs,

PercentOfTotal = (RepairsOfThisTire*100.00)/TotalRepairs

FROM (

SELECT DealerID, TotalRepairs, tire, RepairsOfThisTire,

Sector = NTILE(5) OVER(PARTITION BY DealerID ORDER BY RepairsOfThisTire DESC, tire DESC)

FROM (

SELECT

t1.DealerID, t2.tire,

TotalRepairs = COUNT(*) OVER (PARTITION BY t1.DealerID),

RepairsOfThisTire = COUNT(*) OVER(PARTITION BY t1.DealerID, t2.tire)

FROM dbo.Table1 t1

LEFT JOIN dbo.Table2 t2 ON t2.RepairId = t1.RepairId

) d

WHERE tire IS NOT NULL

) e

WHERE Sector < 5

GROUP BY DealerID, TotalRepairs, tire, RepairsOfThisTire--, PercentOfTotal

ORDER BY DealerID, RepairsOfThisTire DESC, tire;