CODEWITHSUNDEEP
mysqlsql
Sam I Am
Sam I Am

Reputation: 13

Finding the most rented car of each year in SQL

I have tables as follows:

Car(id, make, ....)
Deal(id,datetime,car_id,....)

I want to write a query that would return a year, and a car make for the cars that have the most deals (ie the most deal ids) and the number of deals for that car make.

I started out,

SELECT YEAR(D.datetime) AS the_year, C.make, COUNT(D.id) AS num
FROM Deal D, Car C
WHERE D.car_id=C.id
GROUP BY the_year

Unfortunately, this has returned the year and the total number of deals. So I am thinking to create this within another table and then call MAX(tbl.num), but I am confused on the syntax.
Can somebody help me out please?

Upvotes: 0

Views: 298

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

This is an interesting problem. What you are looking for is specifically called the "mode" in statistics. In MySQL, you would get this by using variables or the group_conat()/substring_index()` trick. I'll show the latter:

SELECT the_year,
       substring_index(group_concat(cd.make order by num desc), ',', 1) as the_mark
FROM (SELECT YEAR(D.datetime) AS the_year, C.make, COUNT(D.id) AS num
      FROM Deal D JOIN
           Car C
           ON D.car_id = C.id
      GROUP BY the_year, c.make
     ) cd
GROUP BY the_year;

EDIT:

The version using variables:

SELECT the_year,
       substring_index(group_concat(cd.make order by num desc), ',', 1) as the_mark
FROM (SELECT YEAR(D.datetime) AS the_year, C.make, COUNT(D.id) AS num,
             @rn := if(@year = YEAR(D.datetime), @rn + 1, 1) as rn,
             @year := YEAR(D.datetime)
      FROM Deal D JOIN
           Car C
           ON D.car_id = C.id CROSS JOIN
           (SELECT @year := 0, @rn := 0) vars
      GROUP BY the_year, c.make
      ORDER BY the_year, num DESC
     ) cd
WHERE rn = 1;

Upvotes: 2

Related Questions