Обсуждение: How to query by month?
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
taizi wrote: > 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. You can use WHERE date_trunc('month', order_date) = '2016-11-01 00:00:00' The downside is that this clause cannot use a simple index on "order_date", you'd have to create an index on date_trunc('month', order_date). Yours, Laurenz Albe
On Tue, Nov 29, 2016 at 9:08 AM, taizi <taizi2006blog@163.com> wrote:
I think you want date_trunc(), not date_part. If you want the sum per month of the whole table doHi 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
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
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
On 11/29/2016 06:08 AM, taizi 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. select sum(order_amount) from table_name where date_part('month', order_date) = '$month'; Note that the $month will be the month number, not name. So for November, it will = 11. JD > thanks for any help! > Leopold > > > > > -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development. Everyone appreciates your honesty, until you are honest with them. Unless otherwise stated, opinions are my own.