CODEWITHSUNDEEP
mysqlsqlgroup-bycountinner-join
Amit Rathod
Amit Rathod

Reputation: 27

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

Upvotes: 0

Views: 529

Answers (2)

18Man
18Man

Reputation: 572

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;

Upvotes: 2

Popeye
Popeye

Reputation: 35910

You can use the group by as follows:

Select m.device_id, m.device_serial_number, count(*) as cnt
  From device_master_data m join device_logs l on l.device_id = m.device_id
Group by m.device_id, m.device_serial_number
Order by count(*) desc
Limit 20

Upvotes: -1

Related Questions