需要使用sql developer在oracle db上按日期计算记录和分组计数 [英] Need to count records and group count by date on oracle db using sql developer

问题描述

我有一张像下面这样的表格

I have a table like the following

   ID                created    sent    type
-----------------------------------------------------
0001463583000051783  31-JUL-12  1   270
0081289563000051788  01-AUG-12  1   270
0081289563000051792  01-AUG-12  1   270
0081289563000051791  01-AUG-12  1   270
0081289563000051806  01-AUG-12  1   270
0001421999000051824  06-AUG-12  1   270
0001421999000051826  06-AUG-12  1   270
0001464485000051828  06-AUG-12  1   270
0082162128000051862  09-AUG-12  2   278
0082162128000051861  09-AUG-12  2   278
0022409222082910259  09-AUG-12  3   278

我想要以下输出

created     Count
---------------------
31-JUL-12   1
01-AUG-12   4
06-AUG-12   3
09-AUG-12   3

在 Oracle 10g 上使用 SQL Developer 实现这一目标有多难

How hard would it be to accomplish this using SQL Developer on Oracle 10g

我尝试了几次查询来生成这样的表，但最终它没有按日期对计数进行分组，当我们每天平均处理 5000-10000 笔交易时，它只是给我一个1"的计数.我可能把它复杂化了.但我想要一些简单的东西，我可以在一个日期范围内每天提取交易量.

I have tried several queries to generate such a table and in the end it does not group the count by date just gives me a '1' for the count when we average 5000-10000 transactions daily. Im probably over complicating it. But i would like something simple where i can pull the amount of transactions on a daily basis within a date range.

当我运行查询时当前发生的事情是

what is happening currently when i run my queries is

created     Count
---------------------
31-JUL-12   1
01-AUG-12   1
01-AUG-12   1
01-AUG-12   1
01-AUG-12   1
06-AUG-12   1
06-AUG-12   1
06-AUG-12   1
09-AUG-12   1
09-AUG-12   1
09-AUG-12   1

推荐答案

我设法通过这个查询获得了这个结果:

I managed to get this results with this query:

select trunc(created), count(*)
from table1
group by trunc(created)

注意trunc函数，即使你不显示它，DATE数据类型也保存时间

Note the trunc function, even if you don't display it, the DATE datatype holds the time as well

这里是一把小提琴

这篇关于需要使用sql developer在oracle db上按日期计算记录和分组计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！