如何查询一年中的GROUP BY [英] How to query GROUP BY Month in a Year

问题描述

我正在使用Oracle SQL Developer. 我基本上有一张包含各列的图片表:

I am using Oracle SQL Developer. I essentially have a table of pictures that holds the columns:

[DATE_CREATED(日期)，NUM_of_PICTURES(int)]

[DATE_CREATED(date), NUM_of_PICTURES(int)]

如果我选择*，我将得到类似于以下内容的输出:

and if I do a select *, I would get an output similar to:

01-May-12    12
02-May-12    15
03-May-12    09
...
...
01-Jun-12    20
...
etc.

我正在尝试将这些图片的总和汇总为MONTHLY数字，而不是每日.

I am trying to aggregate these sums of pictures into MONTHLY numbers instead of DAILY.

我尝试做类似的事情:

select Month(DATE_CREATED), sum(Num_of_Pictures))
from pictures_table
group by Month(DATE_CREATED);

这将输出错误:

ORA-00904: "MONTH": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 5 Column: 9

我的月份功能有误吗?

推荐答案

我倾向于在输出中包含年份.一种方式:

I would be inclined to include the year in the output. One way:

select to_char(DATE_CREATED, 'YYYY-MM'), sum(Num_of_Pictures)
from pictures_table
group by to_char(DATE_CREATED, 'YYYY-MM')
order by 1

另一种方式(更标准的SQL):

Another way (more standard SQL):

select extract(year from date_created) as yr, extract(month from date_created) as mon,
       sum(Num_of_Pictures)
from pictures_table
group by extract(year from date_created), extract(month from date_created)
order by yr, mon;

记住顺序，因为您大概希望这些顺序，并且不能保证分组后返回行的顺序.

Remember the order by, since you presumably want these in order, and there is no guarantee about the order that rows are returned in after a group by.

这篇关于如何查询一年中的GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！