calculation percentages using sql command

Mahesh S

Hi

I have a table with the following attributes: Timestamp and a value.

I would like to calculate the percentage of how many values occuring
above a figurative threshold value between the hours of 6 and 10 AM?

Is there a function available for doing this in a sql statement?

Thanks
Mahesh

Apr 27 '06 #1

Serge Rielau

Mahesh S wrote:

Hi

I have a table with the following attributes: Timestamp and a value.

I would like to calculate the percentage of how many values occuring
above a figurative threshold value between the hours of 6 and 10 AM?

Is there a function available for doing this in a sql statement?

SELECT (SUM(val) * 100)/COUNT(1) FROM T WHERE stamp BETWEEN ? AND ?

Not sure where the problem lies....

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 27 '06 #2

Mahesh S

well, i think i ahvent explained clearly..

consider this table

timestamp value
2005/07/08 6
2005/07/09 7
2005/07/10 8
2005/07/11 6

i would like to calculate the percentage of the a value occuring
between 5 and 7 between two dates?

does that make sense?

cheers

Apr 27 '06 #3

Serge Rielau

Mahesh S wrote:

well, i think i ahvent explained clearly..

consider this table

timestamp value
2005/07/08 6
2005/07/09 7
2005/07/10 8
2005/07/11 6

i would like to calculate the percentage of the a value occuring
between 5 and 7 between two dates?

SELECT SUM(CASE WHEN value BETWEEN 5 AND 7 THEN 1.0 END)/ COUNT(1)
FROM T WHERE timestamp BETWEEN ? AND ?

That ought to do it.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 27 '06 #4