Subquery not working - 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

Subquery not working

Author

Topic

parallon
Starting Member

25 Posts

Posted - 2006-07-20 : 13:37:19

Hello all. I have got some wonderful help getting me to this point, although now I have run into an issue which I really can't understand. I have the following query, which should give me the records for the employee in the query, but for some reason I am getting all records.

SELECT EmpName, EmpNumber, EmpLevel, JobTask, WorkYear, Sum(tblEmpTime.Hours) AS SumOfHours
FROM tblEmpTime
WHERE EXISTS (SELECT EmpName, EmpNumber, Hours, EmpLevel, JobTask, WorkYear
              FROM tblEmpTime
              WHERE EmpName = 'varEmpName' AND WorkYear = 'varWorkYear')
              GROUP BY EmpName, JobTask, EmpNumber, EmpLevel, WorkYear

If I run just the subquery, the results are ok, but I need to sum the hours, so that is why I used the outer query. For some reason it seems that it is ignoring the results of the subquery.

Any suggestions?

Thanks,

Parallon

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-20 : 14:24:35

What do u want to do?

>> WHERE EXISTS (SELECT EmpName, ....
ur condition in Where clause will give true or false, depending on the exstence of the results for the sub query. As u says, if there is(r) result(s) for subquery, the where clause has a true condition hence returning all data in tblEmpTime.

Ur query may be needed to be changed as:


SELECT EmpName, EmpNumber, EmpLevel, JobTask, WorkYear, Sum(tblEmpTime.Hours) AS SumOfHours
FROM tblEmpTime
WHERE EmpName = @varEmpName AND WorkYear = @varWorkYear
GROUP BY EmpName, JobTask, EmpNumber, EmpLevel, WorkYear

and the variables @varEmpName AND @varWorkYear needs to be defined and assigned with values.

Srinika

parallon
Starting Member

25 Posts

Posted - 2006-07-20 : 14:46:27

Ok, that worked good, and I thank you. Slight change; what if I need to display the Locations (also within same table). It seems that once I add that field into the statement, it blows up the SUM statement, and I just have the individual hours once again, assuming because I am having to group by it. See, an employee can perform any task at any location, so I just want a total of the hours of that JobTask, along with a breakdown of all the locations that he/she performed this task at. Is this possible or do I have to programatically sum the hours now?

What I am wanting it to look like is this: (will code display in ASP)

JobTask             SumOfHours
  Location1         
  Location2         
  Location3

Thanks,

Parallon

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-20 : 14:54:18

Try


SELECT EmpName, EmpNumber, EmpLevel, JobTask, WorkYear, location, Sum(tblEmpTime.Hours) AS SumOfHours
FROM tblEmpTime
WHERE EmpName = @varEmpName AND WorkYear = @varWorkYear
GROUP BY EmpName, JobTask, EmpNumber, EmpLevel, WorkYear, location

Srinika

parallon
Starting Member

25 Posts

Posted - 2006-07-20 : 15:29:04

Ok, I tried that, but now instead of my data looking like this:

EmpName       JobTask         SumOfHours
John Doe      Fabrication     14
John Doe      Welding         11
John Doe      Wiring          7

It now looks like this:

EmpName       JobTask         SumOfHours        Location
John Doe      Fabrication     5                 Building A
John Doe      Fabrication     9                 Building B
John Doe      Welding         5                 Building A
John Doe      Welding         6                 Building B
John Doe      Wiring          7                 Building A

See how it broke up the hours? I guess this really won't work without additional code on the front end. I just wish there was a way to SELECT the Location field without having to GROUP it, but with the SUM() function, I guess that's out of the question.

Thanks,

Parallon

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-20 : 15:37:08

Isn't the earlier query returns the first results set ?
U asked to group the results by "Location"!!
do u want to group by locations or not ?

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources