Menu
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?
479
The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.
I used this but the created_date is a field with a full datetime so I had to apply this little hack: DATEADD(dd, 0, DATEDIFF(dd, 0, created_date)) in the group by part and it all worked like a charm!! :) Also change the count(created_date) to Count(). Count with a column only counts the non-null values.
SQL – count() with Group By clause The count() function is an aggregate function use to find the count of the rows that satisfy the fixed conditions. The count() function with the GROUP BY clause is used to count the data which were grouped on a particular attribute of the table.
You can group month and year with the help of function DATE_FORMAT() in MySQL. The GROUP BY clause is also used.
SELECT COUNT(*), trip_date , shop_id
FROM trips
GROUP BY trip_date , shop_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
