SQL request to group and sum numbers by their day of creation

Question

Let me explain this. I have a table where I have two important fields I need : The first one is a double, and the second one is a date.

I would like to make a requests that groups entries by YYYY-MM-DD, and adds each field of the double by group.

For example, I have two entries the same day :

   |   amount    |    date
1  |    100      | 2010-01-01
2  |    200      | 2010-01-01

What I need is :

1  |     300     | 2010-01-01

Is that possible ? For the moment here is my request :

 SELECT DISTINCT ON (d) amount, to_char(datecreation, 'YYYY-MM-DD') d
   FROM mytable
  GROUP BY d, amount
  ORDER BY d ASC;

What I get is :

1  | 100   | 2010-01-01

It doesn't add the grouped amounts.

score 1 · Accepted Answer · answered Aug 02 '12 at 10:09

1

select sum(amount), date from mytable group by date order by sum(amount) ASC;

If your dates have a time part, then

select sum(amount), to_char(date, 'YYYY-MM-DD') from mytable group by to_char(date, 'YYYY-MM-DD') order by sum(amount) ASC;

answered Aug 02 '12 at 10:09

JB Nizet

The second request worked, thanks a lot ! I'll put as accepted answer as soon as stackoverflow let's me – Alex Aug 02 '12 at 10:16
If I take it a little further, and want to increment the amount for each day. Let's say I have the following just after previous operation : 1 | 300 | 2010-01-01 2 | 200 | 2010-01-02 Can I get : 1 | 300 | 2010-01-01 2 | 500 | 2010-01-02 – Alex Aug 02 '12 at 10:34

score 1 · Answer 2 · answered Aug 02 '12 at 10:15

1

SELECT SUM(amount) amount, to_char(date, 'YYYY-MM-DD')
  FROM myTable
 GROUP BY to_char(date, 'YYYY-MM-DD')
 ORDER BY SUM(amount) ASC;

answered Aug 02 '12 at 10:15

Yaroslav

2 Answers2