Find out the average price for each model in the top5 manufacturers in terms of sales quantity and order by average price

Find out the average price for each model in the top5 manufacturers in terms of sales quantity and order by average price.

I thought about first finding the average price of each model joining with manufacturer table then i added a sub-query of finding top 5 manufacturer in terms of sales quantity and order by average price

select ft.IDModel, avg(TotalPrice) as [average price],
       sum(Quantity) as [sale quantity]
from   FACT_TRANSACTIONS as FT
       join DIM_MODEL as dm 
       on ft.IDModel=dm.IDModel
where  IDManufacturer IN
       (
            select top 5 IDManufacturer 
            from DIM_MODEL 
            where IDModel IN
            (
                 select top 5 IDModel
                 from   FACT_TRANSACTIONS 
                 group by IDModel
                 order by sum(Quantity) desc
            ) 
            group by IDManufacturer
        )
group by FT.IDModel
order by [average price];

Actual output:

IDModel average-price   sale-quantity
110      91.3636        12
108     151.0833        12
109     175.4444        12
107     185.10          15
106     236.75          14
111     285.909         11
104     503.5882        17
103     524.7142         8
102     535.1428         9
101     597.9523        33
105     633.7142        16

i know i am not doing it right so i request to please assist me

Solution 1:

SELECT TOP 5
Manufacturer_Name,
AVG(TOTALPRICE) AVG_RATE,
SUM(QUANTITY)   SALE_QUANTITY
FROM FACT_TRANSACTIONS T1 
                          LEFT JOIN DIM_MODEL M1
                          ON T1.IDModel=M1.IDModel
                          INNER JOIN DIM_MANUFACTURER M2
                          ON M1.IDManufacturer=M2.IDManufacturer
GROUP BY Manufacturer_Name
ORDER BY SALE_QUANTITY DESC