SQL请求按创建日期对数字进行分组和求和 [英] SQL request to group and sum numbers by their day of creation

问题描述

让我解释一下.我有一张表，其中有两个我需要的重要字段: 第一个是双精度型，第二个是日期.

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.

我想提出一个请求，将条目按YYYY-MM-DD分组，并按组添加double的每个字段.

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

我需要的是:

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;

我得到的是:

1  | 100   | 2010-01-01

它不添加分组的金额.

推荐答案

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;

这篇关于SQL请求按创建日期对数字进行分组和求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！