SQL Query with count of duplicates

I have trucks entereing different loading zones. Each time the truck enters a loading zone, it makes an entry into our mysql table witht the following fields:  id, truckName, date, latitude, longitude, speedInZone, zoneName. Through out the day the trucks will enter a leave the zone multiple times.  I need to pull the following data:

truckName, DateTime of last entry in this zone, zoneName, Count of how many times this truck entered this zone.

I am thinking maybe a group by truckName, zoneName may be the right direction but I need a little help.
dtechfishAsked:
Who is Participating?
 
tigin44Commented:
try this

SELECT truckName, MAX(date) AS lastentry, zoneName, COUNT(*)
 FROM yourTable
 GROUP BY truckName, zoneName
 
0
 
SharathData EngineerCommented:
try this.
select id,zonename,count(*) cnt,max(date) last_entry
  from your_table
 group by id,zonename

Open in new window

if you did not get what you are looking for, provide some sample data with expected result.
0
 
SharathData EngineerCommented:
replace id with truckname in my query. tigin got it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.