March 27, 2012 at 2:04 pm
Hi,
I have this table:
IDAmount
1234
212
367
4456
5340
6789
734
8267
965
1027
I want to calculate an average to amount and then, I want to get all the IDs that are greater than this average.
I cannot use temp table and I know I cannot use aggregated sub-query..
Do you have any idea of how can I overcome this?
Thank you
March 27, 2012 at 2:09 pm
Did your professor say you had to do it in one step? If not, declare a variable, calculate the average and assign that to the variable, then query the table using that variable in your Where clause.
If it's not homework, then use a subquery. That's the best way to get it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2012 at 8:56 pm
Thank you so much for the reply.
Not studies but impossible restrictions at my work place.
I've used HAVING and sub query.
March 28, 2012 at 6:39 am
mayabee (3/27/2012)
Thank you so much for the reply.Not studies but impossible restrictions at my work place.
I've used HAVING and sub query.
Excellent. Sometimes you have to break the rules.
But if you workplace really has rules against using subqueries (I've heard of even goofier things), I recommend looking for a saner workplace where they'll actually let you do your job correctly.
Especially if you're a DBA. There's a BIG market right now for DBAs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 28, 2012 at 10:30 am
Actually.. I don't have permission to create temp tables.. and I won't have in the near future so I spend a lot of time working around this, instead of working on my real queries.
I don't have any DBA certificate but I have done DBA job for a few years, maybe will get one of those already! 🙂
March 29, 2012 at 6:49 am
I don't have any DBA certs/degrees/whatever either. Been one successfully for over 10 years now. Get paid a lot for it, too.
The people forbidding temp tables probably just don't understand how SQL Server uses worktables, et al.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 30, 2012 at 6:43 am
If you hear of something within the NYC area, give me a shout 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply