Monday, March 26, 2012

question regarding CASE and GROUP BY

I have the below query and I am not sure if this will return an accurate aggregate, I know I cannot just group by my alias GLG_DELEGATE_ID, is this the way to handle aggregates when you have a CASE in the SELECT statement?

SELECT CASE
WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID
END AS GLG_DELEGATE_ID
,COUNT(P.CONSULTATION_ID) ACTIVITY_AMOUNT
FROM
dbo.CONSULTATION C
GROUP BY
C.GLG_DELEGATE_ID
, C.GLG_IDNo, just repeat your CASE code in the WHERE clause:

SELECT CASE WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID
END AS GLG_DELEGATE_ID
,COUNT(P.CONSULTATION_ID) ACTIVITY_AMOUNT
FROM dbo.CONSULTATION C
GROUP BY CASE WHEN C.GLG_DELEGATE_ID IS null THEN C.GLG_ID
ELSE C.GLG_DELEGATE_ID END
,C.GLG_ID

No comments:

Post a Comment