需要使用sql开发人员在oracle db上按日期对记录和组计数进行计数 [英] Need to count records and group count by date on oracle db using sql developer

问题描述

我有一个类似下面的表格

  ID创建发送类型
 -------- --------------------------------------------- 
 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 
  

我想输出以下内容

 创建计数
 ----- ---------------- 
 31-JUL-12 1 
 01-AUG-12 4 
 06-AUG-12 3 
使用SQL 10g在Oracle 10g上实现这一点有多难？
    / p> 
 
 
我已经尝试过几个查询来生成这样的表，最后它不按日期分组计数只是给我一个'1'，当我们平均5000每日-10000笔交易。我可能会使它复杂化。但我想要一个简单的东西，我可以在日期范围内每天提取交易金额。 
 
 
 
当我运行我的查询时，目前发生的是
 计数
 --------------------- 
 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 
  
 
 
解决方案
我设法使用此查询获得此结果：
  select trunc（created），count（*）
 from table1 
 group by trunc（created）
  
注意 trunc 函数，即使不显示它， DATE  datatype保存时间
 
 
 
 这里是一个小提琴
 
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
I would like to have the following for output
created     Count
---------------------
31-JUL-12   1
01-AUG-12   4
06-AUG-12   3
09-AUG-12   3
How hard would it be to accomplish this using SQL Developer on Oracle 10g

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)
Note the trunc function, even if you don't display it, the DATE datatype holds the time as well

Here is a fiddle

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