SQL Nested Select Statement

[alexkeel79]

I am trying to write a nested Select Statement. I am running into trouble because I can't figure out how to use a value from the Main Select Statement in the nested Select. Here is the Select:

SELECT ISPTutorial.BusUnit, ISPTutorial.Division, ISPTutorial.Director, ISPTutorial.EmpGroup AS eg, (SELECT Count(ISPTutorial.Status) AS CountOfStatus
FROM ISPTutorial
WHERE ISPTutorial.Status = 'Completed' AND
ISPTutorial.EmpGroup = '***??????***'
GROUP BY ISPTutorial.EmpGroup)
FROM ISPTutorial
GROUP BY ISPTutorial.BusUnit, ISPTutorial.Division, ISPTutorial.Director, ISPTutorial.EmpGroup;

In my WHERE clause of the nested SELECT I need to replace the '***??????***' with the value of the ISPTutorial.EmpGroup from the main SELECT. Any suggestions? Thanks

 

[PHV]

Try this:
SELECT ISPTutorial.BusUnit, ISPTutorial.Division, ISPTutorial.Director, ISPTutorial.EmpGroup AS eg, (SELECT Count(ISPTutorial.Status) AS CountOfStatus
FROM ISPTutorial
WHERE ISPTutorial.Status = 'Completed' AND
ISPTutorial.EmpGroup = A.EmpGroup
GROUP BY ISPTutorial.EmpGroup)
FROM ISPTutorial As A
GROUP BY ISPTutorial.BusUnit, ISPTutorial.Division, ISPTutorial.Director, ISPTutorial.EmpGroup;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alexkeel79]

Nope. That spit back this error:

"You tried to execute a query that does not include the specified expression 'EmpGroup' as part of an aggregate function."

I also tried the SQL with just ISPTutorial.EmpGroup rather than using A. I'm not quite sure why it is saying it needs to be part of an Aggregate function. Any ideas?

Thanks for you help.

 

[PHV]

And this ?
SELECT DISTINCT BusUnit, Division, Director, EmpGroup AS eg
, (SELECT Count(Status)FROM ISPTutorial WHERE Status='Completed' AND EmpGroup=A.EmpGroup
GROUP BY EmpGroup) AS CountOfStatus
FROM ISPTutorial As A
ORDER BY BusUnit, Division, Director, EmpGroup;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alexkeel79]

That worked. Thanks a lot for your help.

 

[JonFer]

If BusUnit, Division, and Director are attributes of EmpGroup, i.e. each EmpGroup will only be in one record in your output, then this is a more efficient method than a subquery:

SELECT ISPTutorial.BusUnit, ISPTutorial.Division, ISPTutorial.Director, ISPTutorial.EmpGroup AS eg, 
-Sum(ISPTutorial.Status='Completed') as CountOfStatus
FROM ISPTutorial
GROUP BY ISPTutorial.BusUnit, ISPTutorial.Division, ISPTutorial.Director, ISPTutorial.EmpGroup;

(ISPTutorial.Status='Completed') returns a value of -1 if True so it can be used as a counter.

John

http://www.interautomateconsulting.com