June 11, 2009 at 11:21 am
Greetings,
I have a transaction table that hold hour transaction data. Each record in the table has employeeID as the index and fields reg_hrs, ot_hrs, sick_hrs, vac_hrs, hol_hrs as well as a few other fields. Employee is a table holding the employee info.
I want to do something like:
Select
EmployeeID,
Name,
(Select SUM(reg_hrs + ot_hrs + sick_hrs + vac_hrs + hol_hrs) from ptran where EmployeeID = A.EmployeeID) as tot_hrs
from employee A
thanks for any help.
June 11, 2009 at 11:33 am
Hi,
The Sum aggregate is used to SUM the same columns accross rows. What you want to do is sum different columns.
Here is an example
Select
EmployeeID,
Name,
(Select (reg_hrs + ot_hrs + sick_hrs + vac_hrs + hol_hrs) as SummedHrs from ptran where EmployeeID = A.EmployeeID) as tot_hrs
from employee A
That is, considering the datatypes are all int, or numeric formats. If they are date, you will have to extract the hour portion of the date, like this datepart(h,Datetime), which will get you the Hour in the current date.
Hope that helps,
Cheers,
J-F
June 11, 2009 at 1:13 pm
Close but no cigar.
For every ptran record I want to add all the hour fields together. Then sum that value for all the records in the table.
So if the data looked like (EmployeeID, transdate, reg_hrs, ot_hrs, hol_hrs, sick_hrs, vac_hrs)
(emp1, 2009-4-01, 40, 2, 0, 0, 0)
(emp4, 2009-4-01, 40, 0, 0, 0 ,0)
(emp1, 2009-04-08, 38, 0, 0, 2, 0)
then when select is done, the sum for emp1 would be 82
June 11, 2009 at 1:20 pm
Try this:
select
A.EmployeeID,
A.Name,
sum(B.reg_hrs + B.ot_hrs + B.sick_hrs + B.vac_hrs + B.hol_hrs) TotalHrs
from
employee A
inner join ptran B
on (A.EmployeeID = B.EmployeeID)
group by
A.EmployeeID,
A.Name;
June 11, 2009 at 2:07 pm
Since I didn't give you the actual names in previous postings, here is what your suggestion looks like modified to fit the actual names:
select
A.empnum,
A.ename,
A.lname,
A.mi,
A.addr1,
A.addr2,
A.city,
A.state,
A.zip,
A.ssn,
A.bdate,
A.hdate,
A.tdate,
sum(B.reg_hrs + B.ot_hrs + B.sick_hrs + B.vac_hrs + B.hol_hrs) TotalHrs
from
@emps A
inner join prtrxp B
on (A.empnum = B.emp_num)
group by
A.empnum,
A.lname;
When I parse it it is just fine. When I execute it I get:
Msg 8120, Level 16, State 1, Line 60
Column '@emps.ename' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Not sure what is wrong.
June 11, 2009 at 2:12 pm
You need to group by Every column that is not contained in your sum, here's how:
SELECT A.empnum,
A.ename,
A.lname,
A.mi,
A.addr1,
A.addr2,
A.city,
A.state,
A.zip,
A.ssn,
A.bdate,
A.hdate,
A.tdate,
sum(B.reg_hrs
+ B.ot_hrs
+ B.sick_hrs
+ B.vac_hrs
+ B.hol_hrs) TotalHrs
FROM @emps A
INNER JOIN prtrxp B
ON (A.empnum = B.emp_num)
GROUP BY A.empnum,
A.ename,
A.lname,
A.mi,
A.addr1,
A.addr2,
A.city,
A.state,
A.zip,
A.ssn,
A.bdate,
A.hdate,
A.tdate
Hope that helps,
Cheers,
J-F
June 11, 2009 at 2:59 pm
Thanks a bunch. Works like a charm.
June 11, 2009 at 3:07 pm
darryl (6/11/2009)
Since I didn't give you the actual names in previous postings, here is what your suggestion looks like modified to fit the actual names:select
A.empnum,
A.ename,
A.lname,
A.mi,
A.addr1,
A.addr2,
A.city,
A.state,
A.zip,
A.ssn,
A.bdate,
A.hdate,
A.tdate,
sum(B.reg_hrs + B.ot_hrs + B.sick_hrs + B.vac_hrs + B.hol_hrs) TotalHrs
from
@emps A
inner join prtrxp B
on (A.empnum = B.emp_num)
group by
A.empnum,
A.lname;
When I parse it it is just fine. When I execute it I get:
Msg 8120, Level 16, State 1, Line 60
Column '@emps.ename' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Not sure what is wrong.
I'm glad things have worked out, but may I make a suggestion? Please provide all the necessary information needed to profide you with the best help possible.
Two articles I recommend reading:
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 11, 2009 at 3:30 pm
Thanks for the reminder. Was guilty last time and you reminded me then. Just can't seem to get myself to use my live code/data. Must be a personal defect. 😉
June 11, 2009 at 3:58 pm
darryl (6/11/2009)
Thanks for the reminder. Was guilty last time and you reminded me then. Just can't seem to get myself to use my live code/data. Must be a personal defect. 😉
When it comes to live data, you don't have to if you can generate data that is comparable and demonstrates the basic issue/problem. The real problem comes when people attempt to simplify the problem at hand and the answer(s) provided don't help because there is actually more to the problem than was originally stated.
June 11, 2009 at 4:28 pm
[font="Verdana"]Okay, one thing I will add as a caution here.
If any of your five hours fields can be null, then adding them together will result as null, which means all of those hours will fall out of the results.
If they are all defined as not null, then you don't have an issue. If nulls are allowed, you need to put isnull(field, 0) around them (where "field" is the name of the field, in this case, one of reg_hrs, ot_hrs, sick_hrs, vac_hrs, hol_hrs).
Alternatively, you can change this line:
sum(B.reg_hrs + B.ot_hrs + B.sick_hrs + B.vac_hrs + B.hol_hrs) TotalHrs
To be:
sum(B.reg_hrs) + sum(B.ot_hrs) + sum(B.sick_hrs) + sum(B.vac_hrs) + sum(B.hol_hrs) TotalHrs
But that probably still won't guarantee you the result you want.
As an example, assuming I have the following two lines:
(emp1, 2009-4-01, 40, 2, null, null, null)
(emp1, 2009-04-08, 38, null, null, 2, null)
Here's some code that illustrates the issue:
selectB.employee_id,
sum(B.reg_hrs + B.ot_hrs + B.sick_hrs + B.vac_hrs + B.hol_hrs) as TotalHrs_1,
sum(B.reg_hrs) + sum(B.ot_hrs) + sum(B.sick_hrs) + sum(B.vac_hrs) + sum(B.hol_hrs) as TotalHrs_2,
sum(isnull(B.reg_hrs, 0) + isnull(B.ot_hrs, 0) + isnull(B.sick_hrs, 0) + isnull(B.vac_hrs, 0) + isnull(B.hol_hrs, 0)) as TotalHrs_3
from(
select'emp1' as employee_id,
'2009-04-01' as work_date,
cast(40 as decimal(6, 2)) as reg_hrs,
cast(2 as decimal(6, 2)) as ot_hrs,
cast(null as decimal(6, 2)) as sick_hrs,
cast(null as decimal(6, 2)) as vac_hrs,
cast(null as decimal(6, 2)) as hol_hrs
union all
select'emp1' as employee_id,
'2009-04-08' as work_date,
cast(38 as decimal(6, 2)) as reg_hrs,
cast(null as decimal(6, 2)) as ot_hrs,
cast(null as decimal(6, 2)) as sick_hrs,
cast(2 as decimal(6, 2)) as vac_hrs,
cast(null as decimal(6, 2)) as hol_hrs
) B
group by
B.employee_id;
[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply