Search code examples
mysqlsqlgroup-bycountinner-join

SQL GROUP BY INNER JOIN AND COUNT

I am new to MySQL. I am facing an issue in generating a query.

So Basically I have two tables: device_master_data, device_logs.

device_master_data:

device_id device_seral_number

for any device_id there is a unique device_serial_number.

device_logs:

device_id log_date city_id

so in this table there are multiples entries for a device_id.

for example: there are 5 entries for a device_id 1, 10 entries for device_id 2

Goal: To get the TOP 20 most occuring device_id from the device_logs table and then get the device_serial_number corresponding to the device_id.

Result:

count device_serial_number

Please anyone can help it would be really helpful

Solution

  • use this

    SELECT COUNT(dl.device_id) as count, dmd.device_serial_number
   FROM device_logs dl JOIN 
        device_master_data dmd 
        ON dl.device_id = dmd.device_id
GROUP BY d1.device_serial_number
ORDER BY count DESC
LIMIT 20;