SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Summing two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/10/2010 :  12:42:37  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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

USA
35940 Posts

Posted - 11/10/2010 :  13:04:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/10/2010 :  14:28:27  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 11/10/2010 :  14:38:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/10/2010 :  14:58:36  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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'.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 11/10/2010 :  14:59:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/10/2010 :  16:45:53  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
thanks Tara.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 11/10/2010 :  16:49:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/10/2010 :  16:54:12  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 11/10/2010 :  16:57:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/10/2010 :  17:06:29  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 11/10/2010 :  17:29:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/12/2010 :  12:27:29  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 11/12/2010 :  12:52:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/12/2010 :  13:22:13  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 11/12/2010 :  13:26:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/12/2010 :  13:47:16  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
Thank you Tara,

That was it.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 11/12/2010 :  13:48:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome.

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

Subscribe to my blog
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 11/15/2010 :  05:35:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000