数据:
编号 登记日期 登记数
1 2008-10-27 9
2 2008-10-29 15
3 2008-10-30 18
4 2008-10-31 13
5 2008-11-01 3
6 2008-11-02 8
7 2008-11-04 14
8 2008-11-05 7
9 2008-11-08 10
请教各位高手如何按月分组查询获得分组后10和11的合计天数.
渴望结果:
登记日间 登记数 合计天数
2008-10 55 4
2008-11 42 5
编号 登记日期 登记数
1 2008-10-27 9
2 2008-10-29 15
3 2008-10-30 18
4 2008-10-31 13
5 2008-11-01 3
6 2008-11-02 8
7 2008-11-04 14
8 2008-11-05 7
9 2008-11-08 10
请教各位高手如何按月分组查询获得分组后10和11的合计天数.
渴望结果:
登记日间 登记数 合计天数
2008-10 55 4
2008-11 42 5
from tb
group by to_char(登记日间,'YYYY-MM'),sum(登记数)
SELECT TO_CHAR(登记日期,'YYYY-MM') "登记日间",
SUM(登记数) "登记数",
COUNT(1) "合计天数"
FROM TABLE_NAME TT
GROUP BY TO_CHAR(登记日期,'YYYY-MM');
to_char(编号,'YYYY-MM-DD') as 编号,
sum(登记数),
count(*),
from tb
where to_char(编号,'YYYY-MM') in ('2008-10','2008-11')
group by to_char(编号,'YYYY-MM')
各位高手请问一下是哪里不对
错误提示:
ora-01722 无效数字
substr(登记日期, 1, 7) 登记日期,
sum(登记数) 登记数,
count(distinct substr(登记日期, 1, 7)) 合计天数
from t_table
group by substr(登记日期, 1, 7);
group by TO_CHAR(登记日期,'YYYY-MM')