calculating average - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

calculating average

Author

Topic

anaji
Starting Member

15 Posts

Posted - 2012-06-07 : 03:50:19

I need to modify this query below so that I can get the average injected water. The column ,[INJ_WATER] is stored hourly and I want the query to return just a single value which represent the average for the day.

SELECT CONVERT(varchar(20),[Date_Time],101) AS DATE_TIME

,[INJ_WATER] As INJ_WATER
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W1')/100 as numeric(20,2)) As W1
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W2')/100 as numeric(20,2)) As W2
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W3')/100 as numeric(20,2)) As W3
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W4')/100 as numeric(20,2)) As W4
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W5')/100 as numeric(20,2)) As W5
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W6')/100 as numeric(20,2)) As W6
,cast([INJ_WATER]*[EWDB].[dbo].[FO_GET_LAST_LAYER_PERCENT]([Date_Time],[CID],'W7')/100 as numeric(20,2)) As W7
FROM [EWDB].[dbo].[VO_Daily_Injection]
WHERE [CID] = 'EP_PROPERTY(EP_TEMPLATE(AVOCETVM COMPLETION TEMPLATE)$COMPLETION - NAME)'
AND CONVERT(varchar(20),[Date_Time],101) = CONVERT(varchar(20),EP_TIME-1,101)

the current output i'm getting is like this

06/7/2012 322413
06/7/2012 332453
06/7/2012 234535
06/7/2012 345355
06/7/2012 345355

The calculation is made on the hourly basis what i need is to get the average of the enitre day as 1 value only to display

thanks,

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 04:05:59

It looks like yoou shoould get the date followed by 8 more columns - not the 1 that yoou say you are getting.
What do yoou want to average?

Maybe put group by CONVERT(varchar(20),[Date_Time],101) at the end then avg around all other columns.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

anaji
Starting Member

15 Posts

Posted - 2012-06-07 : 04:18:28

nigel,

yes you are right i do get 8 columes from this query, so the first colum is date, the second is the INJ_Water which i want to average, so in other words i want it to show me 1 date and the average of all the values that are in the INJ_Water, to try to explain more, i get a valuse calculated hourly so at the end of the day instead of having multiple values hourly i want to get just 1 average for the whole day

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 05:12:39

still not sure
maybe as a start

SELECT CONVERT(varchar(20),[Date_Time],101) AS DATE_TIME
,avg([INJ_WATER]) As INJ_WATER
WHERE [CID] = 'EP_PROPERTY(EP_TEMPLATE(AVOCETVM COMPLETION TEMPLATE)$COMPLETION - NAME)'
AND CONVERT(varchar(20),[Date_Time],101) = CONVERT(varchar(20),EP_TIME-1,101)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

anaji
Starting Member

15 Posts

Posted - 2012-06-07 : 06:08:07

Nigel,

thanks the avg and group by actually worked it was very straight forward, i guess after writing alot of complex quiries you forget that simple straigh ones work as well, appreciate your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources