Finding 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

Finding average

Author

Topic

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-09 : 21:32:35

Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".

I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.

Shifts | Mon_day | Mon_Night | Tue_Day.....
Average | 5.2 | 10.2 | 15.2........

The codes that i tried..

declare @Weekday tinyint, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())

if (@Weekday= 1 and (@hour>= 7 AND @hour<19))
begin
Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average'
SELECT AVG(Sat_Night)
FROM Weekly
end

if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7))
begin
Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average'
SELECT AVG(Sun_Day)
FROM Weekly
end

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-09 : 21:42:58

what error do you get? You have to put Group by with aggregate function like SUM,Average

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 21:45:09

You can't put a WHERE clause in the INSERT portion. It just wouldn't make sense anyway as you are inserting a new row and therefore there is no WHERE yet.

Your problem is here: Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average'

And also in the other Insert.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-09 : 22:03:13

Ok.So how do i fix this? How to insert the average values in the same row where shift='average'..Please advice...thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 22:04:54

If you are trying to insert something where a row already exists, then use an UPDATE statement. Insert is for new rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-09 : 23:38:18

If i use the following update command, it is giving error near where statement. I dont know how to update by selecting data from other table. Please help. thanks.

declare @Weekday tinyint, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())

if (@Weekday= 1 and (@hour>= 7 AND @hour<19))
begin
Update AverageEngTime set Sat_Night where Shifts = 'Average'
SELECT AVG(Sat_Night)
FROM Weekly
end

if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7))
begin
Update AverageEngTime set Sun_Day where Shifts = 'Average'
SELECT AVG(Sun_Day)
FROM Weekly
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-09 : 23:59:11

I can't figure out what you are trying to do, but based upon your other threads, you've got a serious database design problem.

I have no idea if this is what you want, but try this:
Update AverageEngTime
set Sun_Day = (SELECT AVG(Sun_Day) FROM Weekly)
where Shifts = 'Average'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shan1430
Yak Posting Veteran

86 Posts

Posted - 2008-04-10 : 00:14:48

thanks it works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources