Nested aggregate functions - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Nested aggregate functions

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-03-15 : 15:38:52

I am trying to write a query that will hopefully do a sum of sums. However I can't get it quite yet. This is the query sofar:

select substring(fjobno,0,6) as job,sum (datediff (minute, fsdatetime, fedatetime)) as minutes
from tableX
where fieldA != 'X' and fieldB > 'Y'
group by job

if I try to run this I get this error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'job'.

if I substitute fjobno for job in the group by clause then it works but the SUM does not sum properly because it is based on the full string for jobno and not the substring I would like it to be based on.

How can I do this????
Thanx

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-15 : 15:53:53

Try this:

select substring(fjobno,0,6) as job,sum (datediff (minute, fsdatetime, fedatetime)) as minutes
from tableX
where fieldA != 'X' and fieldB > 'Y'
group by substring(fjobno,0,6)

twhelan1
Yak Posting Veteran

71 Posts

Posted - 2006-03-15 : 15:55:57

select substring(fjobno,0,6) as job,sum (datediff (minute, fsdatetime, fedatetime)) as minutes
from tableX
where fieldA != 'X' and fieldB > 'Y'
group by fjobno

You need to perform your grouping on the base field not the aliased name.
[edit]errr.. yeah what Srinika said... my code won't quite give you the correct grouping[/edit]

~Travis

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-03-15 : 15:56:18

Change your group by to

group by substring(fjobno, 0, 6)

and you should be fine.

Ken

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-03-15 : 16:08:11

thank you all
KenW is right. This DOES work (checked it manually)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources