How to get most rented out cars in SQL query

Question

1.00/5 (1 vote)

See more:

Suppose I have a table with CarID column and each id is rented to a a customer when they come to rent it. What I want to be able to find out is through SQL query that what are the 5 most rented out cars like which cars does the customer like and is rents the most and which cars are rented out the 5 least rented out cars.

so how do I do it with an sql query

Please reply ASSP
Thank you.

What I have tried:

CREATE PROCEDURE dbo.SPCarRental_GetRentedCarsTop5
AS
SELECT *
FROM [CarRental]
WHERE
(
CarID IN
(
SELECT TOP (5) CarID
FROM [CarRental] as table1
GROUP BY CarID
ORDER BY CarID DESC
)
)

Posted 23-Nov-18 19:51pm

Ron23111

Updated 24-Nov-18 5:09am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Answer 1 · 2018-11-23T20:47:00

Solution 1

Break down the sentences and write sub queries until you get what you want.

"5 most rented cars" -> count the cars by id and sort descending

"which cars the customer likes" -> count the cars by id sort descending having the customer's id

Posted 23-Nov-18 20:47pm

Mehdi Gholam

MadMyche · Answer 2 · 2018-11-24T05:09:00

Without your db schema it is kind of hard to answer this; as we can only assume what data you have.

You have referenced the table CarRental in your query. I am guessing that these are distinct reservations for a particular car, identified by the column CarID. If this were my (normalized) DB, I would also have a table for Car with a PK of CarID, as referenced by the rental table.

If all of this is true; the query you have presented would provide you details about the reservations and not so much about the cars themselves. Even if that is what you wanted you are not going to get the 5 most popular as your aggregation is only on the CarID (essentially doing nothing) and sorting by CarID. It is a reasonable assumption then that the subquery is going to always respond with the last 5 CarIDs in descending order
What you would want to do is to keep the aggregate based on CarID, however; you want to do the order on the Count of those CarIDs, descending

SQL

SELECT   *
FROM     CarRental
WHERE    CarID IN (
   SELECT   TOP (5) CarID
   FROM     CarRental
   GROUP BY CarID
   ORDER BY Count(CarID) DESC
)

If I was right on my db schema, that you have a Car table; and you wanted the Car details instead- the query is an easy edit to have the main query reference that table and keeping the subquery as is:

SQL

SELECT   *
FROM     Car
WHERE    CarID IN (
   SELECT   TOP (5) CarID
   FROM     CarRental
   GROUP BY CarID
   ORDER BY Count(CarID) DESC
)