Group By again... - 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

Group By again...

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-11-29 : 12:21:57

This code below works

[CODE]
SELECT e.usr_name,
c.shortname ,
sum(cl.duration)
from tabc1 cl , user e, campaign c
WHERE
cl.campaign = c.code and
cl.agent = e.code
GROUP by e.usr_name,c.shortname
[/CODE]

But when I add another sum .
[CODE]
SELECT e.usr_name as 'AGENT',
c.shortname as 'Campaign',
sum(cl.duration) as 'Staffed Time Per Campaign',
sum(ul.duration) as 'GLOBAL STAFFED TIME ON ALTITUDE'
from easy71..ag_in_cp_log cl , easy71..e_user e, easy71..campaign c, easy71..user_log ul
WHERE
cl.campaign = c.code and
cl.agent = e.code and
ul.e_user = e.code

GROUP by e.usr_name,c.shortname
[/CODE]

This gives the error
[CODE]
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.
[/CODE]

It should work, it still has the correct group by and joins ??

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-29 : 12:27:38

what is the data type of ul.durat?
You may have to convert/cast it first.

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-11-29 : 12:38:25

They are of type int .. u.duration and cl.duration..
thats what makes it really strange ..

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-29 : 12:48:41

not for nothing, but would you care to post the ddl?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-11-30 : 04:17:03

what's a ddl ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-30 : 04:23:47

ddl, data definition language, please look at the FAQs.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-30 : 04:28:48

Also, I do not know what kind of joins you are trying to achieve with this statement. Please can you put the joins into standard T-SQL as it seems you are trying to achieve RIGHT JOINS in the above statement (This will give you the NULLS).

It may be something as simple as wrapping an ISNULL() around your fields.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources