开发者

How do I sum records by week using pl/sql?

I have a query in Oracle for a report that looks like this:

SELECT TRUNC (created_dt) created_dt
    ,  COUNT ( * ) AllClaims
    ,  SUM(CASE WHEN filmth_c开发者_Python百科d in ('T', 'C') THEN 1 ELSE 0 END ) CT
    ,  SUM(CASE WHEN filmth_cd = 'W' THEN 1 ELSE 0 END ) Web 
    ,  SUM(CASE WHEN filmth_cd = 'I' THEN 1 ELSE 0 END ) Icon 
FROM claims c
WHERE c.clsts_cd NOT IN ('IN', 'WD')
   AND  TRUNC (created_dt) between 
    to_date('1/1/2006', 'dd/mm/yyyy') AND 
    to_date('1/1/2100', 'dd/mm/yyyy')
GROUP BY TRUNC (created_dt)
ORDER BY TRUNC (created_dt) DESC;

It returns data like this:

Create_Dt  AllClaims   CT    Web    Icon
1/26/2011  675         356   285    34
1/25/2011  740         322   379    39
...

What I need is a result set that sums all of the daily values into a weekly value. I am pretty new to PL/SQL and not sure where to begin.


Something like

SELECT TRUNC (created_dt, 'IW') created_dt
    ,  COUNT ( * ) AllClaims
    ,  SUM(CASE WHEN filmth_cd in ('T', 'C') THEN 1 ELSE 0 END ) CT
    ,  SUM(CASE WHEN filmth_cd = 'W' THEN 1 ELSE 0 END ) Web 
    ,  SUM(CASE WHEN filmth_cd = 'I' THEN 1 ELSE 0 END ) Icon 
FROM claims c
WHERE c.clsts_cd NOT IN ('IN', 'WD')
   AND  TRUNC (created_dt) between 
    to_date('1/1/2006', 'dd/mm/yyyy') AND 
    to_date('1/1/2100', 'dd/mm/yyyy')
GROUP BY TRUNC (created_dt, 'IW')
ORDER BY TRUNC (created_dt, 'IW') DESC;

will aggregate the data based on the first day of the ISO week.


Here is a simple example that I quite often use:

SELECT
 TO_CHAR(created_dt,'WW'),
 max(created_dt),
 COUNT(*)
from MY_TABLE
group by
  TO_CHAR(created_dt,'WW');

The to_char(created_dt,'WW') create the groups, and the max(created_dt) displays the last day of the week. Use min(created_dt) if you want to display the first day of the week.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜