SQL TO SUMMARIZE THE DATA BY YEAR 3

[Ann28]

Hello everyone!

I am using DB2 V8.1 UDB.

Here is the data:

ssn: hrs: year(yymm):
999999999 100.00 6603
999999999 100.00 6606
999999999 100.00 6609
999999999 050.00 6612

999999999 100.00 6703
999999999 100.00 6706
999999999 100.00 6709

999999999 217.00 6803

888888888 100.00 6703
888888888 100.50 6706
888888888 100.00 6709
888888888 100.00 6712

I am looking for an select/update/procedure to create the following output:
OUTPUT (summarize hrs by year)

999999999 350.00 66
999999999 300.00 67
999999999 217.00 68

888888888 400.00 67


THANKS A LOT FOR YOUR HELP!!

 

[Ann28]

i figure out how to sum the data , but

select ssn, sum(hrs), wk_ccyy from AUDIT_CLP group by ssn, wk_ccyy created

999999999 350.00 66
999999999 300.00 67
999999999 217.00 68

Is it possible to create the table with the following:

ssn year: 66 year: 67 year: 68
99999 300.00 300.00 217.68

THANKS FOR YOUR HELP

 

[Mercury2]

__

Look here
__

 

[juliane26]

as often in reporting this is a presentation question and no longer regular SQL.

of course, if you do have a fixed number of years you can generate a query doing that for you, but the the result set will always have a column for each year, no matter whether it is there in the ssn or not.
It would the look something like this

with
a67 as (select ssn, hrs from t1 where yr=67),
a66 as (select ssn, hrs from t1 where yr=66),
a68 as (select ssn, hrs from t1 where yr=68)

select t1.ssn, sum(a66.hrs) as year_66, sum(a67.hrs) as year_67, sum(a68.hrs) as year_68
from t1 left outer join a66
on t1.ssn = a66.ssn and t1.yr=66
left outer join a67
on t1.ssn = a67.ssn and t1.yr=67
left outer join a68
on t1.ssn = a68.ssn and t1.yr=68
group by t1.ssn
;

Still, for every additional year you have to re-generate the query.

That's the nature of SQL (versus reporting).




Juliane

 

[BrianTyler]

Juliane is correct in that SQL cannot easily pivot a table in the way you want, and his solution will work.

Personally, i would use a CASE statement:

select
ssn
,sum(case when ssn = 67 then hrs else 0 end) as S67
,sum(case when ssn = 68 then hrs else 0 end) as s68
,sum(case when ssn = 69 then hrs else 0 end) as S69
etc
from

group by


Once again you will have to amend the query for each new year.

A further option is to move your resuls to Excel and create a Pivot Table.

Brian

 

[Ann28]

Thank you ALL FOR YOUR HELP!

I have to create the cross-tab query
(there are a lot of columns in the table from the YEAR 1966 TO 2004)
I can either create one huge case statement or utilize the article recommended by Mercury2

Any suggestions are appreciated!!!!
AnnJ

 

[Ann28]

SOMETHING LIKE THIS....

Sample data:

SSN       HRS         WK_CCYY

--------- ----------- -------

999999999     2424.50 1967   
999999999     2721.00 1969   
999999999     2086.00 1970   
999999999     2756.50 1973   
999999999     2724.00 1974   
999999999     1411.00 1977   
888888888     1003.50 1967   
888888888     1182.50 1969   
001036355     2910.50 1967

SQL:

select
      ssn
     (case when wk_ccyy = '1966' then hrs else 0 end) as Y_1966
     (case when wk_ccyy = '1967' then hrs else 0 end) as Y_1967
     (case when wk_ccyy = '1968' then hrs else 0 end) as Y_1968

   from table
    
   group by SSN

THANKS
ANN

 

[Ann28]

PLEASE HELP THIS DOES NOT WORK

SELECT SSN,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1966,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1967,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1968,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1969,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1970,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1971,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1972,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1973,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1974,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1975,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1976,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1977,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1978,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1979,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1980,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1981,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1982,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1983,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1984,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1985,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1986,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1987,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1988,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1989,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1990,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1991,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1992,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1993,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1994,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1995,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1996,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1997,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1998,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_1999,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_2000,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_2001,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_2002,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_2003,
SUM (CASE WHEN WK_CCYY = '1968' THEN HRS END) AS Y_2004
FROM AUDIT_CLP WHERE SSN = '001012347'
GROUP BY WK_CCYY, SSN

 

[Ann28]

...oops, it's 1968 all of it....

 

[juliane26]

Brian,

nice SQL.


Juliane

 

[Ann28]

thanks Juliane
have any suggestions on how to make it better?

 

[juliane26]

I do think Brians SQL is very smart - as I saw right now that his solution is also the option from mercury's hint.

And unfortunately that is best what SQL can do for you.

What else can be done is dependet on the format of the report you have to give to your management.

Since our managers and departments love excel I'd rather go with a pivot table as Brian suggested. It is simple, give it a try:
- register your database as an ODBC datasource
- open a new spreadsheet in Excel
- got to menue item Data -> Pivot Table
- select 'external data source' and 'PivotTable'
- On NEXT you select 'import data'
- select your ODBC datasource
- enter uid/pwd (if necessary)
- select the table
- press a few times enter as needed
- pull ssn into rows on the left, year into cols above, hours into the data field

and there you go ....

(the exact wording might be different since I don't have an english version)

When you fool around you will notice that for your select you have more options (like filter your years or join or whatever), so actually that is a nice solution to be rather quick with that kind of report.

Well, any further advice probably should go to an Excel forum, not to bore folks here ...


Juliane

 

[Ann28]

Thank you Juliane!
I shall try it

 

[cristi22]

This is something very similar to what I have to do....is there a way to automate it, somehow post it to the web via crystal/any ideas???

thanks a lot for your help!!!

 

[cristi22]

Juliane -
can i add the sp ?