Aggregate Values (SUM, AVG and Tot_Val) in a View

[karlomutschler]

Hi all,
I have created a view that displays the following info.

Turnover Total
Contract ID Jan Feb Mar ... Dec YTD AVG Value
533976 62,88 46,76 17,76 ? 68,88
1669463 60,25 79,63 49,94 68,00 50,25
197536 42,84 13,36 ? 47,84 43,28
425364 35,80 91,44 29,03 ? 56,80
1284315 82,66 92,29 89,33 22,66 23,76

I would like to add the columns YTD, AVG and Total (Contract) Value.

The script to crate the view reads as:
CREATE VIEW TurnOver01
(Contract_ID
,TO_Jan00
,TO_Feb00
,TO_Mar00
,TO_...00
,TO_Dec00)
-- ,YTD
-- ,AVG
-- ,Total_Value
AS SELECT Contract_ID
,01.TO_Jan00
,02.TO_Feb00
,03.TO_Mar00
,XX.TO_,,,00
,12.TO_Dec00
-- ,SUM(??????)
-- ,AVG(??????)
-- ,SUM(Tot_Val98+Tot_Val99+Tot_Val00)
FROM Turnover_Jan00 01
LEFT OUTER JOIN Turnover_Feb00 02 ON 01.Contract_ID = 02.Contract_ID
LEFT OUTER JOIN Turnover_Mar00 03 ON 01.Contract_ID = 03.Contract_ID
...
LEFT OUTER JOIN Turnover_Dec00 12 ON 01.Contract_ID = 12.Contract_ID ;

TIA and kind regards.
Karlo

 

[tdatgod]

Hi,
What does your BASE table look like? Are you using Left Outer Join to eliminate NULLS or to make sure you only compare rows from this 12 way joins once?

Maybe there is a better way to do this Report.

 

[karlomutschler]

Hi tdatgod,

thanks for your valued reply.

Well I have 12 Tables (Jan - Dec00) each containing the columns
Contract_ID, turnover_date and turnover_amount.

TIA
Karlo

 

[tdatgod]

Hi,
Is every contract ID guarenteed to be in every table basically an amount for every month for every contract ID?

Is there only one row per Contract ID in each table or is is possible to have multiple rows pers Contract id per table?

 

[karlomutschler]

Hi tdatgod,

If the respective contract generaterd t/o during a month, there will be an entry else there is none.
Contr_ID Jan Feb Mar .... Dec
197536 42,84 13,36 ? 47,84 43,28

In the table for January, for example, there is only one row of data (Contract_ID, turnover_date and turnover_amount) for each contract numer

TIA
Karlo