CODEWITHSUNDEEP
mysqlgroup-by
teustis
teustis

Reputation: 253

MySQL- GROUP and COUNT by date

I am trying to figure out the proper syntax for getting query result I need. Here is the scenario:

I am designing a SaaS application and have a table called trips, like so:

table.trips  
- trip_id (pk, ai)  
- client_id (fk)  
- shop_id (fk)
- trip_date (date)

I need to be able to get the number of trips in the table for each day (by shop_id) and then deliver the number of TRIPS per trip_date, like:

DATE         | TRIP SUM  
--------------------------
01-01-2011   | 3
01-02-2011   | 2  
01-03-2011   | 5

Any thoughts on the proper syntax?

Upvotes: 16

Views: 47577

Answers (5)

Luis Alberto Juárez Aguirre
Luis Alberto Juárez Aguirre

Reputation: 141

If the date column also stores hour and minute you will need to apply a substring function to the date, to only have the yyyy-mm-dd part of the date column, like this:

SELECT COUNT(*), substring(trip_date,1,10)
FROM trips
GROUP BY substring(trip_date,1,10);

Upvotes: 11

Duniyadnd
Duniyadnd

Reputation: 4043

SELECT COUNT(*), trip_date , shop_id 
FROM trips 
GROUP BY trip_date , shop_id

Upvotes: 32

manji
manji

Reputation: 47968

SELECT shop_id, trip_date, count(trip_id)
FROM trips
GROUP BY shop_id, trip_date

Upvotes: 2

Mikael Östberg
Mikael Östberg

Reputation: 17146

Something like this?

select 
   DATE(trip_date) as [DATE],
   count(1) as [TRIP SUM]
from
   trips
group by
   DATE(trip_date), shop_id

I'm not a mySQL guy, but you will need to use a T-SQL DatePart equivalent, where I have used the DATE() function in order to strip the time portion of the date.

Upvotes: 4

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

select trip_date,count(*)
from trips
where shop_id=[the shop id]
group by trip_date

This will work as long as the date is on the format you showed. If it has also time then you have to remove the time.

Also I do not know if the client_id has to come in in this query, if you have to group by it also then you have to put it along side trip_date on select or group. If you want to filter by it put it in the where.

Upvotes: 2

Related Questions