MySQL- GROUP and COUNT by date

Question

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?

What do you mean by "by shop id"? There is no shop_id in your resultset. — Quassnoi
– Quassnoi, Commented Mar 23, 2011 at 15:11

Taryn · Accepted Answer · 2012-02-17 02:09:22Z

32

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

edited Feb 17, 2012 at 2:09

Taryn

249k57 gold badges375 silver badges409 bronze badges

answered Mar 23, 2011 at 15:12

Duniyadnd

4,0431 gold badge24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ken White Over a year ago

You either need to remove the WHERE or put in a WHERE condition. The way it is is invalid SQL.

teustis Over a year ago

Perfect:-) Thanks for your help. Added WHERE shop_id=# and it worked perfectly. Cheers.

Onimusha Over a year ago

SELECT COUNT(*) AS customvar always helps with the returned results

Luis Alberto Juárez Aguirre · Accepted Answer · 2018-05-14 21:22:30Z

12

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);

answered May 14, 2018 at 21:22

Luis Alberto Juárez Aguirre

1511 silver badge4 bronze badges

1 Comment

Furison Over a year ago

This just saved me ages trying to compile a count of rows by date but not time

Mikael Östberg · Accepted Answer · 2011-03-23 15:16:21Z

4

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.

answered Mar 23, 2011 at 15:16

Mikael Östberg

17.2k6 gold badges67 silver badges80 bronze badges

Comments

Dumitrescu Bogdan · Accepted Answer · 2011-03-23 15:14:28Z

2

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.

answered Mar 23, 2011 at 15:14

Dumitrescu Bogdan

7,2652 gold badges26 silver badges32 bronze badges

Comments

manji · Accepted Answer · 2011-03-23 15:17:20Z

2

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

edited Mar 23, 2011 at 15:17

answered Mar 23, 2011 at 15:12

manji

48k5 gold badges98 silver badges104 bronze badges

1 Comment

mickmackusa Over a year ago

Please take a moment to expand on this code-only answer regarding how it works and why it is a good idea. When new users see code-only answers posted by high-repped users they think that this is "how it is done on SO". Please role model better posting behavior for their sake -- not to mention for the benefit of future researchers.

Collectives™ on Stack Overflow

MySQL- GROUP and COUNT by date

5 Answers 5

3 Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related