oracle SQL group by "date" 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/722708/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL group by "date" question
提问by OscarRyz
I'm trying to make a report but I'm having problems with my archi nemesis SQL.
我正在尝试制作报告，但我的主要克星 SQL 出现问题。
I have a table where the close date of a transaction is stored.
我有一个表格，其中存储了交易的结束日期。
I want to know how many transaction per month there was so I did:
我想知道每月有多少交易，所以我做了：
SELECT trunct( closedate, 'MONTH' ) FROM MY_TRANSACTIONS
I'm using oracle.
我正在使用甲骨文。
I'm getting a list like this:
我得到一个这样的列表：
2002-09-01 00:00:00.0
2002-09-01 00:00:00.0
...
2002-10-01 00:00:00.0
2002-10-01 00:00:00.0
...
2002-11-01 00:00:00.0
2002-11-01 00:00:00.0
etc.
等等。
So I thought "If I add a COUNT( ) in the select and GROUP BY at the end of the statement that should do" but it doesn't. My guess is because each record is treated as a different value : -S
所以我想“如果我在应该做的语句末尾的选择和 GROUP BY 中添加一个 COUNT( )”，但事实并非如此。我的猜测是因为每条记录都被视为不同的值：-S
Any hint please?
请问有什么提示吗？
Thanks.
谢谢。
回答by Shea
You want to group by all non-agg fields. And you don't want to truncate the date, you want the month part of the date.
您想按所有非 agg 字段分组。并且您不想截断日期，而是需要日期的月份部分。
so something like
所以像
select to_char(datefield, 'Month'), count(*) from ... group by to_char(datefield, 'Month');
select to_char(datefield, 'Month'), count(*) from ... group by to_char(datefield, 'Month');
