CODEWITHSUNDEEP
mysqlsql
GyaNoLi
GyaNoLi

Reputation: 37

HOW to Find the most frequent string in mySQL

I would like to get the most frequent car type (varchar) within the 25 years older owners. I wrote a query, but it counts all the types of names. How can I complement this mySQL query to count only the same name type of cars?

SELECT type, COUNT(type) 
FROM `car` 
INNER JOIN owner 
ON car.owner= tulajdonos.id 
WHERE 2018 - owner.birth_date >= 25

Upvotes: 1

Views: 322

Answers (3)

forpas
forpas

Reputation: 164174

You have to sort descending your query and take only the topmost row with LIMIT 1. 

SELECT type, COUNT(type) AS counter 
FROM `car` 
INNER JOIN owner 
ON car.owner= tulajdonos.id 
WHERE 2018 - owner.birth_date >= 25
GROUP BY type
ORDER BY COUNT(type) DESC 
LIMIT 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You could start by doing the date arithmetic correctly. After that, you probably just want limit:

SELECT c.type, COUNT(*) AS counter 
FROM car c INNER JOIN
     owner o
     ON c.owner= o.id 
WHERE o.birth_date < curdate() - interval 25 year;
ORDER BY counter DESC 
LIMIT 1

Upvotes: 0

Ed Callahan
Ed Callahan

Reputation: 189

If you want the one most frequent type you could use

SELECT top 1 type, COUNT(type) 
    FROM `car` 
    INNER JOIN owner 
    ON car.owner= tulajdonos.id 
    WHERE 2018 - owner.birth_date >= 25
    order by count(type) desc, type

if you are sure there are no ties.

Also, your way of determining age isn't perfect, you could use something like:

WHERE 
case
    when DATEPART(DY, owner.birth_date) > DATEPART(DY, GETDATE())
        then DATEDIFF(YYYY, owner.birth_date, GETDATE()) - 1
    else DATEDIFF(YYYY, owner.birth_date, GETDATE())
end >= 25

Upvotes: 0

Related Questions