For every CarID calculate mode of Price within specific date range

Oleja 41 Reputation points
2021-11-09T08:03:59.053+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,576 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,416 Reputation points
    2021-11-10T01:35:33.793+00:00

    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.

    No comments

1 additional answer

Sort by: Most helpful
  1. Tom Phillips 17,511 Reputation points
    2021-11-09T17:07:02.22+00:00

    They are asking you for this:

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