CODEWITHSUNDEEP
mysqlsql
Radical_Activity
Radical_Activity

Reputation: 2738

How to get a daily SUM of numbers with MySQL?

I have a table, which looks like the following:

Table users:

id   points   date
1    100      2014-07-01
2    500      2014-07-02
3    200      2014-07-01
4    100      2014-07-03
5    100      2014-07-01
6    400      2014-07-02
7    800      2014-07-02
8    200      2014-07-02

Now, how is it possible to select each unique date and count the sum of the points on those days?

So I's need a result something like this:

points   date
400      2014-07-01
1900     2014-07-02
100      2014-07-03

Upvotes: 1

Views: 2881

Answers (3)

user2864740
user2864740

Reputation: 61875

This group-and-aggregate operation is a standard query pattern and can be solved following these steps1.

  1. Use a GROUP BY date to aggregate the dates into groups, by date.
  2. Select SUM(points) to tally the points in each aggregated [date] group.
  3. Select the date column, which represents each group, to include it in the results.
  4. Finally, apply ORDER BY date to ensure the results are ordered.

1 It is good etiquette - and results in better answers/discussion - when discussing attempted solutions (ie queries) in questions, and why they didn't work [correctly].

Upvotes: 0

Tibor B.
Tibor B.

Reputation: 1690

Try this:

SELECT SUM(points) points, date
FROM users
GROUP BY date
ORDER BY date ASC

Upvotes: 0

Mureinik
Mureinik

Reputation: 311188

SELECT   SUM(`points`) AS points, `date`
FROM     users
GROUP BY `date`

Upvotes: 2

Related Questions