Oracle subquery Help 1

[born2program]

I have been trying to get the following query to work. The problem I am having is with the count on the answer. I need a subquery that shows a count of all answers that = 2 without filtering the main query
by answers that =2. Hopefully this is clear. Thanks for your help.



SELECT COUNT(BCEMPLOYEEREVIEW.EMPLOYEEREVIEWID) TOT_REVIEWS,
COUNT(DISTINCT BCREVIEWSCORE.QUESTIONID) TOT_QUESTIONS,
COUNT(SELECT CONFIG.BCREVIEWSCORE.ANSWER FROM CONFIG.BCREVIEWSCOREWHERECONFIG.BCREVIEWSCORE.ANSWER='2') ANSWERCOUNT,
BCEMPLOYEEREVIEW.STEPREVIEWED, BCEMPLOYEEREVIEW.WORKTYPE
FROM CONFIG.BCEMPLOYEEREVIEW BCEMPLOYEEREVIEW, CONFIG.BCREVIEWSCORE BCREVIEWSCORE
WHERE BCEMPLOYEEREVIEW.EMPLOYEEREVIEWID=BCREVIEWSCORE.EMPLOYEEREVIEWID
GROUP BY BCEMPLOYEEREVIEW.STEPREVIEWED, BCEMPLOYEEREVIEW.WORKTYPE

 

[LKBrwnDBA]

Try this:

Select Count(Bcemployeereview.Employeereviewid) Tot_Reviews
     , Count(Distinct Bcreviewscore.Questionid) Tot_Questions
     , Sum(Decode(Config.Bcreviewscore.Answer,'2',1,0)) Answercount
     , Bcemployeereview.Stepreviewed, Bcemployeereview.Worktype 
  From Config.Bcemployeereview Bcemployeereview, Config.Bcreviewscore Bcreviewscore
 Where Bcemployeereview.Employeereviewid=Bcreviewscore.Employeereviewid
 Group By Bcemployeereview.Stepreviewed, Bcemployeereview.Worktype

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[born2program]

Thanks LKBrwnDBA! That did it.