January 18, 2017 at 8:27 am
Hello friends
hopefully you can assist me
In my query, I need to frame a condition like
Activities where there is neither a subunit, or Main External Company , get the count of activities which are having category 'EAS-1F'
I wrote the query like:
;WITH SubUnitorMainCompanyNull
AS
(
SELECT ASu.activityIncId, ASu.activitySqlId,
COUNT(CASE WHEN subUnitName IS NULL THEN activityCode ELSE NULL END) AS SubUnitNullCount,
COUNT(CASE WHEN CL1.internalname IS NULL THEN activityCode ELSE NULL END) AS MainCompanyNullCount
FROM Activities ASu
INNER JOIN ActivitiesCategories ACTSu ON ACTSu.activityCategoryIncId = ASu.activityCategoryIncId
AND ACTSu.activityCategorySqlId = ASu.activityCategorySqlId AND ASu.isDeleted=0x0 AND ACTSu.isDeleted=0x0
AND ACTSu.activityCategoryCode = 'EAS-1F'
LEFT JOIN [dbo].SubUnits SU ON SU.subUnitIncId = ASu.subUnitWhoDoIncId
AND SU.subUnitSqlId = ASu.subUnitWhoDoSqlId
AND SU.isDeleted = 0x0
LEFT join [dbo].Activitiespositions ASP
INNER join [dbo].positions PO
ON PO.positionIncId=ASP.positionIncId and PO.positionSqlId=ASP.positionSqlId and PO.isDeleted=0x0
ON ASu.activityIncId=ASP.activityIncId and ASu.activitysqlId=ASP.activitysqlId
and ASu.isdeleted=0x0 and ASP.isdeleted=0x0
LEFT join [dbo].Contacts CT
on CT.contactIncId=ASP.contactIncId and CT.contactSqlId=ASP.contactSqlId and CT.isDeleted=0x0
LEFT join [dbo].Clients CL1
on CL1.clientIncId=CT.clientIncId and CL1.clientSqlId=CT.clientSqlId and CL1.isDeleted=0x0
and PO.positionCode IN ('EAS001','EAS002')
WHERE ( COUNT(CASE WHEN subUnitName IS NULL THEN activityCode ELSE NULL END) = 0 OR COUNT(CASE WHEN CL1.internalname IS NULL THEN activityCode ELSE NULL END) = 0 )
GROUP BY ASu.activityIncId, ASu.activitySqlId
)
I am getting a error message as below
Msg 147, Level 15, State 1, Line 170
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
The problem is with the code wriiten in the WHERE clause. Help please to rewrite it so that it will work
Thanks
January 18, 2017 at 8:29 am
Since there's an aggregate that you're filtering on, use the HAVING clause, not WHERE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2017 at 8:35 am
GilaMonster - Wednesday, January 18, 2017 8:29 AMSince there's an aggregate that you're filtering on, use the HAVING clause, not WHERE
Thanks Gila..It worked.
Need a help more...How I can get the columns SubUnitNullCount and MainCompanyNullCount as a single column as stated in the condition..
Activities having neither a subunit, or Main External Company , get the count of activities
January 18, 2017 at 8:42 am
VSSGeorge - Wednesday, January 18, 2017 8:35 AMGilaMonster - Wednesday, January 18, 2017 8:29 AMSince there's an aggregate that you're filtering on, use the HAVING clause, not WHEREThanks Gila..It worked.
Need a help more...How I can get the columns SubUnitNullCount and MainCompanyNullCount as a single column as stated in the condition..
Activities having neither a subunit, or Main External Company , get the count of activities
Something like this? COUNT(CASE WHEN subUnitName IS NULL AND CL1.internalname IS NULL THEN activityCode ELSE NULL END) AS SingleNullCount,
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply