Summing two tables - 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)

Summing two tables

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-10 : 12:42:37

I have two tables that I need to do a join and sum on

Table 1 has fields
name, employeenumber, minutes

table 2 has fields
name, employeenumber, totalminutes

I need to join name and employeenumber and sum totalminutes and minutes as summinutes.

I've written this query but it's giving me errors:

select name, employeenumber, sum(scratchpad2.minutes, scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.name = scratchpad4.name

Can anyone offer a better way to do this?

Thanks

Doug

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-10 : 13:04:57

You need a GROUP BY, likely GROUP BY name, employeenumber.

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

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-10 : 14:28:27

Tara,

Ok I added the group by clause and I'm getting this error:

The sum function requires 1 arguments. Obviously this part isn't right"

sum(scratchpad2.minutes, scratchpad4.totalminutes) as summinutes

What's a better way to write that?

Thanks

Doug

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-10 : 14:38:22

Is this what you want?

SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes)

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

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-10 : 14:58:36

Yes. Here is the query I have:

select name, employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.name = scratchpad4.name
group by name, employeenumber

and now I get:

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Name'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Employeenumber'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Name'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Employeenumber'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Name'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Employeenumber'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-10 : 14:59:41

You have to specify which table those columns are coming from.

select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.name = scratchpad4.name
group by scratchpad2.name, scratchpad2.employeenumber

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

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-10 : 16:45:53

thanks Tara.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-10 : 16:49:56

You're welcome, glad to help.

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

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-10 : 16:54:12

oh and one last thing, if I wanted to have summinutes/60 as total,

select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes, sum(summinutes/60) as total
from scratchpad2
inner join scratchpad4
on scratchpad2.name = scratchpad4.name
group by scratchpad2.name, scratchpad2.employeenumber

how would I add that last operand to that query?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-10 : 16:57:46

Any reason why you can't do that last column from the application?

For your query to work, you'd have to repeat the calculation again or use a derived table. I'd suggest doing the calculation in your application and just returning raw data from SQL.

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

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-10 : 17:06:29

There isnt a reason why I can't do it from my application. That's what I'll do. I was just seeing if it would be better to do it from the query, but evidently not.

Thank you again.

Doug

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-10 : 17:29:33

Here's the derived table approach:

select name, employeenumber, summinutes, sum(summinutes/60) as total
from (
select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.name = scratchpad4.name
group by scratchpad2.name, scratchpad2.employeenumber
) t

I prefer the derived table approach over the repeated calculation.

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

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-12 : 12:27:29

Tara,

When I try that query that you wrote, I get the folllowing error:

Server: Msg 8118, Level 16, State 1, Line 1
Column 't.name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 't.employeenumber' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 't.summinutes' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-12 : 12:52:33

I think you've edited my query somewhat to get those errors.

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

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-12 : 13:22:13

Tara,

here is my query as I ran it:

select name, employeenumber, summinutes, sum(summinutes/60) as total
from (
select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.name = scratchpad4.name
group by scratchpad2.name, scratchpad2.employeenumber
) t

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-12 : 13:26:40

Ah yes, I see it. I was confused by the "t."'s in the error messages.

Here you go:

select name, employeenumber, summinutes, sum(summinutes/60) as total
from (
select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.name = scratchpad4.name
group by scratchpad2.name, scratchpad2.employeenumber
) t
group by name, employeenumber, summinutes

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

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-12 : 13:47:16

Thank you Tara,

That was it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-12 : 13:48:09

You're welcome.

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

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-15 : 05:35:23

There is no need for outer group bys. This should work

select name, employeenumber, summinutes, summinutes/60 as total
from (
select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.name = scratchpad4.name
group by scratchpad2.name, scratchpad2.employeenumber
) t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources