CODEWITHSUNDEEP
mysqldatabasemariadb
loffa
loffa

Reputation: 25

SQL sum one column over time period grouped by other column

I have a log of purchases during different times of the day. Say that each item is sold x times on a receipt. I want to sum all of the ones sold for each model during each day to get the total for each model.

Some example data:

Name      Count Timestamp
----      ----- ----------
Car1        2   2016-05-21 10:23
Car2        1   2016-05-21 11:30
Car1        2   2016-05-21 15:40
Car3        4   2016-08-12 12:03
Car1        2   2016-08-12 14:45
Car3        4   2016-08-12 17:03

The result I want to get from this data is how many of each car is sold each day.

Name      Count Timestamp
----      ----- ----------
Car1        4   2016-05-21
Car2        1   2016-05-21
Car3        8   2016-08-12
Car1        2   2016-08-12

So far I have only managed to do the total sum for each model (using SUM(Count) and GROUP BY Name). But I want to get this for a specific timeframe, each day.

Upvotes: 1

Views: 205

Answers (1)

RealCheeseLord
RealCheeseLord

Reputation: 795

You can just Format your TimeStamp as a Date and then GROUP BY it, something like:

SELECT NAME, SUM(Count), DATE(Timestamp) AS 'Date'
  FROM table
 GROUP BY NAME, `Date`

Documentation for DATE function

Upvotes: 2

Related Questions