| Author |
 Topic  |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
 Posted - 11/10/2010 : 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
USA
35007 Posts |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 11/10/2010 : 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
USA
35007 Posts |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 11/10/2010 : 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
USA
35007 Posts |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 11/10/2010 : 16:45:53
|
thanks Tara.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 11/10/2010 : 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
USA
35007 Posts |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 11/10/2010 : 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
USA
35007 Posts |
Posted - 11/10/2010 : 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
USA
217 Posts |
Posted - 11/12/2010 : 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
USA
35007 Posts |
|
|
dougancil
Posting Yak Master
USA
217 Posts |
Posted - 11/12/2010 : 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
USA
35007 Posts |
Posted - 11/12/2010 : 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
USA
217 Posts |
Posted - 11/12/2010 : 13:47:16
|
Thank you Tara,
That was it. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/15/2010 : 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 |
|
|
| |
Topic |
|
Summing two tables
