Aggregate function + Subquery has me stumped

[christine1]

Can anyone help me formulate a query

I want to write a query that selects * from the test_table
between the dates 01/01/1995 thru 06/30/2000
(I need to 1-sum the following columns:
1-sum(col_1+col_2+col_3+col_4)
for each item last record in that year. ***we are going back five years, for each item that is being represented by an event record in this table***
divide that number ***for each units end of the year record*** col_5 , multiply it by 100.0

***And this should give me the stats for each item represented in the table***

All of this in one query. I know I need a subquery, aggregate funtion - sum, and an order by statement
but I am still having trouble visualizing it.
Please help.

 

[jerasi]

Maybe this will help but, I'm not sure if I understood your problem fully...

select *,(sum(col_1+col_2+col_3+col_4)/col_5)*100 AS Statistics
from table1
where col_date between '01/01/95' AND '06/30/00'
group by col_1, col_2, col_3, col_4, col_5, col_date, (if you select * then you need all the names of the columns in here)

Regards.

 

[christine1]

Thanks for your help and yes you were on the right track even though I left some info. Ultimately the query looks like this:

SELECT YEAR = datepart(year, tti.begin_date),
Unit_Years = sum(tti.col1_hrs)/8760.0,
Avail = "TBD",
Relia = (1.0 - (sum(scp.col2)/sum(tti.col1_hrs)))*100.0
FROM cube_test_info hi,
cube_test_2 tti
WHERE hi.range_index = 102
AND DATEPART(year, tti.begin_date) >= 1991
AND hi.manufacturer = 'BuyAlot'

GROUP BY datepart(year, tti.begin_date)

Not to bad huh?

Christine