Re: How to query by month?

Поиск
Список
Период
Сортировка
От Keith
Тема Re: How to query by month?
Дата
Msg-id CAHw75vutXge4W545MHrK4LM91O1K+sHMRvLDRmkFrEv2vLzn5Q@mail.gmail.com
обсуждение исходный текст
Ответ на How to query by month?  (taizi <taizi2006blog@163.com>)
Список pgsql-admin
Дерево обсуждения


On Tue, Nov 29, 2016 at 9:08 AM, taizi <taizi2006blog@163.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@keith=# create table orders (order_amount numeric, order_date timestamptz);
CREATE TABLE
Time: 69.647 ms

keith@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@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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Albe Laurenz
Дата:
Сообщение: Re: How to query by month?
Следующее
От: Marc Fromm
Дата:
Сообщение: how to upgrade from 8.4 to 9.2