• Curious, is this what you actually want? I added a third dealer with only one order for one tire. This dealer does not show up in the results set.

    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);

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

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

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

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

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

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

    select * from dbo.Table1;

    select * from dbo.Table2;

    select DealerId, tire, count(*) as RepairsOfThisTire,

    max(cnt) as TotalRepairs,

    count(*) * 1.0 / max(cnt) as PercentOfTotal

    from (select t1.*, t2.tire,

    row_number() over (partition by t1.DealerId

    order by (case when t2.tire is null then 1 else 0 end),

    t1.RepairId

    ) as seqnum,

    count(*) over (partition by t1.DealerId) as cnt

    from dbo.Table1 t1 left join

    dbo.Table2 t2

    on t1.RepairId = t2.RepairId

    ) t

    where seqnum <= 0.8*cnt and tire is not null

    group by DealerId, tire

    order by DealerId, tire, RepairsOfThisTire desc;

    go

    DROP TABLE dbo.Table1;

    DROP TABLE dbo.Table2;