oracle 关于统计报表的有关问题
oracle 关于统计报表的问题
SQL> select * from bill;
BILLID BILLDATE SEATID SUM VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111108137 20111108103947 9 1.0 2 1 137 2
20111108139 20111108104009 4 1.0 2 2 139 2
20111108141 20111108104024 7 1.0 2 2 141 2
20111108143 20111108104326 7 1.0 2 2 143 2
20111109172 20111109090803 5 1.0 1 2 172 1
20111110192 20111110124946 10 1.0 2 2 192 2
20111101193 20111101125024 1 1.0 2 2 193 2
20111030194 20111030125108 2 1.0 2 2 194 2
20110915195 20110915125128 3 1.0 2 2 195 2
20110901196 20110901125142 4 1.0 2 2 196 2
20110922197 20110922125156 5 1.0 2 2 197 2
20110928198 20110928125210 1 1.0 2 2 198 2
20111006199 20111006125230 2 1.0 2 2 199 2
20111013200 20111013125243 3 1.0 2 2 200 2
20111027201 20111027125256 4 1.0 2 2 201 2
20111103202 20111103125316 5 1.0 2 2 202 2
20111108132 20111108101744 1 6.0 1.0 1 2 132 1
20111108133 20111108101746 2 21.0 1.0 2 2 133 1
20111109134 20111109101750 3 12.0 1.0 2 2 134 1
20111108135 20111108101754 4 1.0 2 1 135 2
BILLID BILLDATE SEATID SUM VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111109173 20111109141248 5 1.0 2 2 173 2
20111130203 20111130125341 6 1.0 2 2 203 2
20111207204 20111207125352 7 1.0 2 2 204 2
怎么将上面的数据按查询单位：周、月、年查询统计出来（只用查在单位时间内的sum之和，也就是sum（sum））；
------解决方案--------------------
1.按照每周进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'ww')
2.按照每月进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'mm')
3.按照每季度进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'q')
4.按照每年进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'YYYY')
SQL> select * from bill;
BILLID BILLDATE SEATID SUM VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111108137 20111108103947 9 1.0 2 1 137 2
20111108139 20111108104009 4 1.0 2 2 139 2
20111108141 20111108104024 7 1.0 2 2 141 2
20111108143 20111108104326 7 1.0 2 2 143 2
20111109172 20111109090803 5 1.0 1 2 172 1
20111110192 20111110124946 10 1.0 2 2 192 2
20111101193 20111101125024 1 1.0 2 2 193 2
20111030194 20111030125108 2 1.0 2 2 194 2
20110915195 20110915125128 3 1.0 2 2 195 2
20110901196 20110901125142 4 1.0 2 2 196 2
20110922197 20110922125156 5 1.0 2 2 197 2
20110928198 20110928125210 1 1.0 2 2 198 2
20111006199 20111006125230 2 1.0 2 2 199 2
20111013200 20111013125243 3 1.0 2 2 200 2
20111027201 20111027125256 4 1.0 2 2 201 2
20111103202 20111103125316 5 1.0 2 2 202 2
20111108132 20111108101744 1 6.0 1.0 1 2 132 1
20111108133 20111108101746 2 21.0 1.0 2 2 133 1
20111109134 20111109101750 3 12.0 1.0 2 2 134 1
20111108135 20111108101754 4 1.0 2 1 135 2
BILLID BILLDATE SEATID SUM VIP PAYED MADE BILLUUID BILLCHECK
---------------------------------------- -------------- ------- --------- ---- ----- ---- -------- ---------
20111109173 20111109141248 5 1.0 2 2 173 2
20111130203 20111130125341 6 1.0 2 2 203 2
20111207204 20111207125352 7 1.0 2 2 204 2
怎么将上面的数据按查询单位：周、月、年查询统计出来（只用查在单位时间内的sum之和，也就是sum（sum））；
------解决方案--------------------
1.按照每周进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'ww')
2.按照每月进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'mm')
3.按照每季度进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'q')
4.按照每年进行统计
SELECT TO_CHAR(BillDate), SUM(sum) from Bill GROUP BY TO_CHAR(BillDate, 'YYYY')