Most Common Value SubQuery MySql

Question

Is there any other way to write this query ? I tried doing it in a subquery but it doesn't work because of the multiple columns. It seems like this query only works by itself. Please correct me

Records

PK recordId

dateViewed

CarViewed

I tried this

   SELECT R.`dateViewed` FROM Records ,(
   SELECT R.CarViewed, COUNT(R.CarViewed) as cnt FROM  Records R
   GROUP BY R.CarViewed
   ORDER BY cnt DESC
   LIMIT 1 ) AS favouriteCarOfTheDay
   GROUP BY R.`dateViewed

Then I tried this

   SELECT R.`dateViewed` ,COUNT(R.CarViewed) as cnt FROM Records ,
   (
   SELECT R.CarViewed FROM  Records R
   GROUP BY R.CarViewed
   ORDER BY cnt DESC
   LIMIT 1 ) AS favouriteCarOfTheDay
   GROUP BY R.`dateViewed

Along many other queries I tried, I have no idea how to get it working.

In a nutshell for a specific date, I would like to get the most common cars that were viewed.

Like : dateViewed favouriteCarOfTheDay

2012-09-22 | Nissan

2012-09-23 | BMW

echo_Me · Answer 1 · 2013-04-12T14:55:58.720

0

try this

   SELECT R.`dateViewed` ,COUNT(R.CarViewed) as cnt ,R.CarViewed FROM Records R
   GROUP BY R.`dateViewed 
   ORDER BY COUNT(R.CarViewed) DESC

edited Apr 12 '13 at 14:55

answered Apr 12 '13 at 14:41

echo_Me

37,078
5
58
78

As far as I can see that will return one record in total. I think he wants a record per day. – Andy Nichols Apr 12 '13 at 14:42
its grouped by date viewed , soits not one record – echo_Me Apr 12 '13 at 14:43
But won't the LIMIT 1 at the end mean it only returns a single record? – Andy Nichols Apr 12 '13 at 14:45
I have tried that query as well , the results are not grouped by the date anymore – Marc Apr 12 '13 at 14:54
try this updated query, i have removed the group by cardviewed – echo_Me Apr 12 '13 at 14:56

score 0 · Answer 2 · edited May 23 '17 at 12:12

I think the following should work (disclaimer, not all my own work, adapted from an answer at another question)

SELECT DISTINCT
    R.dateViewed,
    R.CarViewed
FROM Records R
WHERE
    R.dateViewed =
        (SELECT R2.dateViewed FROM
            (
                SELECT R1.dateViewed, COUNT(*) AS numViewed
                FROM Records R1 
                WHERE R1.CarViewed = R.CarViewed
                GROUP BY R1.dateViewed
                ORDER BY R1.numViewed DESC
            ) AS R2
            LIMIT 1
        )
ORDER BY r.dateViewed

score 0 · Answer 3 · answered Apr 12 '13 at 15:09

Such things are really awful to do in MySQL so it might actually by slower than two correlated subquery but at least it returns both the car and it's viewcount:

SELECT counts.`dateViewed`,counts.`CarViewed` as favourite_car, counts.cnt
FROM
(SELECT R.`dateViewed` ,R.`CarViewed`, COUNT(*) as cnt 
FROM Records
GROUP BY R.`dateViewed` ,R.`CarViewed`
) as counts JOIN
(SELECT R.`dateViewed`, MAX(cnt) as cnt
FROM
(SELECT R.`dateViewed` ,R.`CarViewed`, COUNT(*) as cnt 
FROM Records
GROUP BY R.`dateViewed` ,R.`CarViewed`
) as q
GROUP BY R.`dateViewed`) as maxes
ON counts.cnt=maxes.cnt

Most Common Value SubQuery MySql

3 Answers3