averages - 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

averages

Author

Topic

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-12 : 17:01:24

select
avg(case when T1>=8 and t1<11 then average else null end) morning ,
avg(case when T1>=11 and t1<27 then average else null end) midday ,
avg(case when T1>=17 and t1<20 then average else null end) afternoon from Times

this give me the average of every time_group (8-11,11-17,17-20)

select transStore,avg(ivalue*quantity) as avgPerStore
from transactions as A,transactionItems as B
where A.transID=B.transID
group by transStore

select distinct convert(varchar,transdatetime,101) as thedate,avg(ivalue*quantity) as avgPerdate
from transactions a,transactionItems b
where a.transID=b.transID
group by convert(varchar,transdatetime,101)
order by avgPerDate desc

the two select statements above find the average per store and the average per date. i want modify the first statement and find the average per day (per monday, per tuesday......per sunday) and include all the three statements in one

the answer should look like...

store 100, monday, 8-11, 24 euro
store 100, monday, 11-17, 100 euro
store 100, monday, 17-20, 150 euro
store 100, tuesday, 8-11, 24 euro
store 100, tuesday, 11-17, 100 euro
store 100, tuesday, 17-20, 150 euro
store 100, friday, 8-11, 24 euro
store 100, friday, 11-17, 100 euro
store 100, friday, 17-20, 150 euro

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-12 : 17:05:48

You do realize how statistically incorrect that is, do you not?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

constantinos1987
Starting Member

25 Posts

Posted - 2008-12-12 : 17:13:47

so i can't do that???
can i do anything of those i mentioned/???

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-12-13 : 15:22:33

Of course you CAN do that.

It's just wrong, that's all.

Just because the average morning is 24 Euro does not mean that the average on the morning of Monday was 24.

Why don't you just take an average by date and time group?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources