Top 5 per Selection

[Noah114]

I have two tables, one with a table for physicians with totals sales, the other, a breakout of the physician sales by drug. Is there a way to create a query that will break out the physicians, list their top 5 products, and then an all other category that will match their total?

 

[r937]

select p.id
     , p.total_sales
     , ps1.sales_amount
  from physicians as p
inner
  join physician_sales as ps1
    on ps1.physician_id = p.id
inner
  join physician_sales as ps2
    on ps2.physician_id = p.id
   and ps2.sales_amount > ps1.sales_amount
group
    by p.id
     , p.total_sales
     , ps.sales_amount
having count(*) < 5

r937.com | rudy.ca