blocks|key|282958|text|select+to_char(date,'Mon')+as+mon,
+++++++extract(year+from+date)+as+yyyy,
+++++++sum("Sales")+as+"Sales"
from+yourtable
group+by+1,2|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|282959|应Radu的要求，我将解释这个问题：|unstyled|282960|to_char(date,'Mon')+as+mon,：将"date“属性转换为定义的短格式month。|offset|length|style|CODE|282961|extract(year+from+date)+as+yyyy：Postgresql的"+extract“函数用于从"date”属性中提取YYYY年份。|282962|sum("Sales")+as+"Sales"：SUM()函数将所有"Sales“值相加，并提供区分大小写的别名，并使用双引号保持区分大小写。|282963|group+by+1,2：GROUP+BY函数必须包含选择列表中不属于聚合的所有列(也称为SUM/AVG/MIN/MAX等函数中不包含的所有列)。这告诉查询应该对每个唯一的列组合应用SUM()，在本例中是month和year列。"1,2“部分是一种速记形式，而不是使用列别名，不过最好使用完整的"to_char(...)”和"extract(...)“表达式以提高可读性。|282964|entityMap^0|0|0|0|R|0|0|V|0|0|N|0|0|C|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|V|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|W|8|@$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|F|7|Z|8|@$I|10|J|11|K|L]]|9|@]|A|$]]|$1|O|3|P|5|F|7|12|8|@$I|13|J|14|K|L]]|9|@]|A|$]]|$1|Q|3|R|5|F|7|15|8|@$I|16|J|17|K|L]]|9|@]|A|$]]|$1|S|3|-4|5|F|7|18|8|@]|9|@]|A|$]]]|T|$]]

<pre><code>select to_char(date,'Mon') as mon,
 extract(year from date) as yyyy,
 sum("Sales") as "Sales"
from yourtable
group by 1,2
</code></pre>

At the request of Radu, I will explain that query:

<code>to_char(date,'Mon') as mon,</code> : converts the "date" attribute into the defined format of the short form of month.

<code>extract(year from date) as yyyy</code> : Postgresql's "extract" function is used to extract the YYYY year from the "date" attribute.

<code>sum("Sales") as "Sales"</code> : The SUM() function adds up all the "Sales" values, and supplies a case-sensitive alias, with the case sensitivity maintained by using double-quotes.

<code>group by 1,2</code> : The GROUP BY function must contain all columns from the SELECT list that are not part of the aggregate (aka, all columns not inside SUM/AVG/MIN/MAX etc functions). This tells the query that the SUM() should be applied for each unique combination of columns, which in this case are the month and year columns. The "1,2" part is a shorthand instead of using the column aliases, though it is probably best to use the full "to_char(...)" and "extract(...)" expressions for readability.

blocks|key|283000|text|实际上，to_char可以让你一下子找到年份和月份！|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|283001|select+to_char(date('2014-05-10'),'Mon-YY')+as+year_month;+--'May-14'
select+to_char(date('2014-05-10'),'YYYY-MM')+as+year_month;+--'2014-05'|code-block|syntax|javascript|283002|或者在上面用户示例的情况下：|283003|select+to_char(date,'YY-Mon')+as+year_month
+++++++sum("Sales")+as+"Sales"
from+some_table
group+by+1;|283004|entityMap^0|4|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

<code>to_char</code> actually lets you pull out the Year and month in one fell swoop!

<pre><code>select to_char(date('2014-05-10'),'Mon-YY') as year_month; --'May-14'
select to_char(date('2014-05-10'),'YYYY-MM') as year_month; --'2014-05'
</code></pre>

or in the case of the user's example above:

<pre><code>select to_char(date,'YY-Mon') as year_month
 sum("Sales") as "Sales"
from some_table
group by 1;
</code></pre>

blocks|key|283057|text|我不敢相信被接受的答案有这么多人支持--这是一种可怕的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|283058|下面是正确的方法，使用date_trunc|offset|length|283059|+++SELECT+date_trunc('month',+txn_date)+AS+txn_month,+sum(amount)+as+monthly_sum
+++++FROM+yourtable
+GROUP+BY+txn_month|code-block|syntax|javascript|283060|这是一种糟糕的做法，但如果你使用|283061|+GROUP+BY+1|283062|在一个非常简单的查询中。|283063|您还可以使用|283064|+GROUP+BY+date_trunc('month',+txn_date)|283065|如果您不想选择日期。|283066|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC^0|0|B|A|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@$D|16|E|17|1|18]]|A|$]]|$1|F|3|G|5|H|7|19|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1A|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|1B|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1D|8|@]|9|@]|A|$]]|$1|S|3|T|5|H|7|1E|8|@]|9|@]|A|$I|J]]|$1|U|3|V|5|6|7|1F|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

I can't believe the accepted answer has so many upvotes -- it's a horrible method.

Here's the correct way to do it, with <a href="http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC" rel="noreferrer">date_trunc</a>:

<pre><code> SELECT date_trunc('month', txn_date) AS txn_month, sum(amount) as monthly_sum
 FROM yourtable
 GROUP BY txn_month
</code></pre>

It's bad practice but you might be forgiven if you use

<pre><code> GROUP BY 1
</code></pre>

in a very simple query. 

You can also use

<pre><code> GROUP BY date_trunc('month', txn_date)
</code></pre>

if you don't want to select the date.

blocks|key|283111|text|有另一种方法可以使用postgres中的date_part()函数来实现此结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|283112|+SELECT+date_part('month',+txn_date)+AS+txn_month,+date_part('year',+txn_date)+AS+txn_year,+sum(amount)+as+monthly_sum
+++++FROM+yourtable
+GROUP+BY+date_part('month',+txn_date)|code-block|syntax|javascript|283113|谢谢|283114|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

There is another way to achieve the result using the date_part() function in postgres.

<pre><code> SELECT date_part('month', txn_date) AS txn_month, date_part('year', txn_date) AS txn_year, sum(amount) as monthly_sum
 FROM yourtable
 GROUP BY date_part('month', txn_date)
</code></pre>

Thanks

blocks|key|283153|text|请看本教程->+https://www.postgresqltutorial.com/postgresql-group-by/的示例6|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|283154|您需要调用组上的函数，而不是调用您在select上创建的虚拟属性的名称。我正在做上面所有答案推荐的操作，但我得到了一个column+'year_month'+does+not+exist错误。|style|CODE|283155|对我起作用的是：|283156|SELECT+
++++date_trunc('month',+created_at),+'MM/YYYY'+AS+month
FROM+
++++"orders"++
GROUP+BY+
++++date_trunc('month',+created_at)|code-block|syntax|javascript|283157|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresqltutorial.com/postgresql-group-by/^0|8|1J|0|0|1N|Y|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@$A|11|B|12|F|G]]|9|@]|C|$]]|$1|H|3|I|5|6|7|13|8|@]|9|@]|C|$]]|$1|J|3|K|5|L|7|14|8|@]|9|@]|C|$M|N]]|$1|O|3|-4|5|6|7|15|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

Take a look at example 6) of this tutorial -&gt; <a href="https://www.postgresqltutorial.com/postgresql-group-by/" rel="nofollow noreferrer">https://www.postgresqltutorial.com/postgresql-group-by/</a>
You need to call the function on your GROUP BY instead of calling the name of the virtual attribute you created on select.
I was doing what all the answers above recommended and I was getting a <code>column 'year_month' does not exist</code> error.
What worked for me was:
<pre class="lang-sql prettyprint-override"><code>SELECT 
 date_trunc('month', created_at), 'MM/YYYY' AS month
FROM 
 &quot;orders&quot; 
GROUP BY 
 date_trunc('month', created_at)
</code></pre>

blocks|key|66860|text|为什么不直接使用date_part函数呢？https://www.postgresql.org/docs/8.0/functions-datetime.html|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|66861|SELECT+date_part('year',+txn_date)+AS+txn_year,
+++++++date_part('month',+txn_date)+AS+txn_month,
+++++++sum(amount)+as+monthly_sum
FROM+payment
GROUP+BY+txn_year,+txn_month
order+by+txn_year;|code-block|syntax|javascript|66862|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/8.0/functions-datetime.html^0|8|9|L|1N|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

Why not just use <code>date_part</code> function. <a href="https://www.postgresql.org/docs/8.0/functions-datetime.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/8.0/functions-datetime.html</a>
<pre><code>SELECT date_part('year', txn_date) AS txn_year,
 date_part('month', txn_date) AS txn_month,
 sum(amount) as monthly_sum
FROM payment
GROUP BY txn_year, txn_month
order by txn_year;
</code></pre>

I have the following database table on a Postgres server: 

<pre><code>id date Product Sales
1245 01/04/2013 Toys 1000 
1245 01/04/2013 Toys 2000
1231 01/02/2013 Bicycle 50000
456461 01/01/2014 Bananas 4546
</code></pre>

I would like to create a query that gives the <code>SUM</code> of the <code>Sales</code> column and groups the results by month and year as follows:

<pre><code>Apr 2013 3000 Toys
Feb 2013 50000 Bicycle
Jan 2014 4546 Bananas
</code></pre>

Is there a simple way to do that?

Group query results by month and year in postgresql

数据库

服务器

我在Postgres服务器上有以下数据库表：id      date          Product Sales1245    01/04/2013    Toys    1000     1245    01/04/2013    Toys    20001231    01/02/2013    Bicycle ...

问在postgresql中按月和年分组查询结果
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在postgresql中按月和年分组查询结果EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在postgresql中按月和年分组查询结果
EN