Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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.
0
dtechfish
Asked:
dtechfish
  • 2
1 Solution
 
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now