Select Count Subquery

I need to get the [Opportunity Count] field to return a count based on the SELECT Count(*) subquery written.  

See code below and look for the [Opportunity Count] column.
/*
Type Codes
Account - 1
Appointmenet - 4201
Opportunity - 3
*/

SELECT  Subject,
        RegardingObjectIdName,
        Description,
        CreatedByName,
        ScheduledEnd,
        ActualEnd,
        CreatedOn,
        RegardingObjectTypeCode,
        [Regarding]=CASE WHEN RegardingObjectTypeCode='1' THEN 'Account' WHEN RegardingObjectTypeCode='4201' THEN 'Appointment' WHEN RegardingObjectTypeCode='3' THEN 'Opportunity' ELSE 'n/a' END, 
        [Opportunity Count]= SELECT COUNT(*) FROM dbo.Opportunity o WHERE StateCode='0' AND o.AccountId=RegardingObjectId
FROM    dbo.Appointment 
ORDER BY ScheduledEnd desc

Open in new window

r270baAsked:
Who is Participating?
 
wdosanjosCommented:
Please try the following:

/*
Type Codes
Account - 1
Appointmenet - 4201
Opportunity - 3
*/

SELECT  Subject,
        RegardingObjectIdName,
        Description,
        CreatedByName,
        ScheduledEnd,
        ActualEnd,
        CreatedOn,
        RegardingObjectTypeCode,
        CASE WHEN RegardingObjectTypeCode='1' THEN 'Account' WHEN RegardingObjectTypeCode='4201' THEN 'Appointment' WHEN RegardingObjectTypeCode='3' THEN 'Opportunity' ELSE 'n/a' END As [Regarding], 
        (SELECT COUNT(*) FROM dbo.Opportunity o WHERE StateCode='0' AND o.AccountId=RegardingObjectId) As [Opportunity Count]
FROM    dbo.Appointment 
ORDER BY ScheduledEnd desc

Open in new window

0
 
dqmqCommented:
You need parens around the select:


        [Opportunity Count]= (SELECT COUNT(*) FROM dbo.Opportunity o WHERE StateCode='0' AND o.AccountId=dbo.Appointment.RegardingObjectId)
0
 
dqmqCommented:
Or, performance-wise, better yet,

SELECT  Subject,
        RegardingObjectIdName,
        Description,
        CreatedByName,
        ScheduledEnd,
        ActualEnd,
        CreatedOn,
        RegardingObjectTypeCode,
        [Regarding]=CASE WHEN RegardingObjectTypeCode='1' THEN 'Account' WHEN RegardingObjectTypeCode='4201' THEN 'Appointment' WHEN RegardingObjectTypeCode='3' THEN 'Opportunity' ELSE 'n/a' END,
[Opportunity Count]
FROM    dbo.Appointment inner join
  (Select AccountID, COUNT(*) as [opportunity Count] FROM dbo.Opportunity
 WHERE StateCode='0'
  group by AccountID
  ) as O
on o.AccountId=RegardingObjectId
ORDER BY ScheduledEnd desc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.