asked on

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

ASKER CERTIFIED SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dqmq

You need parens around the select:

[Opportunity Count]= (SELECT COUNT(*) FROM dbo.Opportunity o WHERE StateCode='0' AND o.AccountId=dbo.Appointment.RegardingObjectId)

dqmq

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