Re: How to query by month?

On Tue, Nov 29, 2016 at 9:08 AM, taizi <taizi2006blog(at)163(dot)com> wrote:

> Hi all,
> im a beginner for SQL, and trying to use it with my daily work.
> Now i catched this question(my title of mail).
> Suppose there is a table about orders, i want to sum the total amount of
> each month.
> There was a statement of mine to november.
> select sum(order_amount) FROM table_name WHERE order_date between
> '2016-11-01' AND '2016-11-30';
> I know it comes a result what i want, but i hope someone can tell me
> another way to use.
> After searched, i found there is a function named date_part, but it
> doesn't work.
> thanks for any help!
> Leopold
>
>
>
>
>
I think you want date_trunc(), not date_part. If you want the sum per month
of the whole table do

keith(at)keith=# create table orders (order_amount numeric, order_date
timestamptz);
CREATE TABLE
Time: 69.647 ms

keith(at)keith=# insert into orders values (1, generate_series('2016-01-01
00:00:00', '2016-12-31 23:59:59', '1 day'::interval));
INSERT 0 366
Time: 15.211 ms

keith(at)keith=# SELECT date_trunc('month', order_date), sum(order_amount)
FROM orders GROUP BY date_trunc('month', order_date) ORDER BY 1;
date_trunc | sum
------------------------+-----
2016-01-01 00:00:00-05 | 31
2016-02-01 00:00:00-05 | 29
2016-03-01 00:00:00-05 | 31
2016-04-01 00:00:00-04 | 30
2016-05-01 00:00:00-04 | 31
2016-06-01 00:00:00-04 | 30
2016-07-01 00:00:00-04 | 31
2016-08-01 00:00:00-04 | 31
2016-09-01 00:00:00-04 | 30
2016-10-01 00:00:00-04 | 31
2016-11-01 00:00:00-04 | 30
2016-12-01 00:00:00-05 | 31
(12 rows)

Time: 0.656 ms

If you only need it for part of the table, add a WHERE condition in as
needed.

Keith