For every CarID calculate mode of Price within specific date range

Question

Hello there, i stuck in problem - Calculate mode Price within specific date range (e.i 2020-21). I think, i was done with the first part of task, but exist next additional condition - "if you have more than one mode, display average price of all modes".

SELECT Price AS ModePrice, COUNT() AS Count 
FROM CARDATABASE 
GROUP BY Price 
HAVING COUNT() >= ALL (SELECT COUNT(*) FROM CARDATABASE GROUP BY Price

Answer 1

Hi @Oleja ，

Welcome to the microsoft TSQL Q&A forum!

Please also check:

 SELECT Price AS ModePrice,   
 COUNT() OVER(PARTITION BY Price )AS Count,  
 AVG(Price) OVER(PARTITION BY CarID) AS averagevalue  
 FROM CARDATABASE   

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

They are asking you for this:

    SELECT CarID, AVG(Price) AS ModePrice, COUNT() AS Count 
     FROM CARDATABASE 
     GROUP BY CarID