how to use case statement in group by
I have following case statements in my SELECT clause, also I have a group by clause... So, Is it okay to specify UF.CO开发者_StackOverflow中文版NT_PID & UF.M_STATUS_CD in my GROUP BY Clause ? I need to specify the entire CASE statement in GROUP BY ?
CASE WHEN UF.CONT_PID IN
('04007005', '01019045','01019046') OR
(UF.M_STATUS_CD IN ('01', '02')
THEN
1
ELSE
0
END
Rather than repeat the expression, you could do:
SELECT col /*, other cols */ FROM
(
SELECT col = CASE WHEN /*...long-winded expression...*/
THEN 1 ELSE 0 END /*, other cols */
FROM dbo.table
) AS x
GROUP BY col;
I believe you can group by in two ways:
GROUP BY UF.CONT_PID, UF.M_STATUS_CD
OR
GROUP BY CASE WHEN UF.CONT_PID IN ('04007005', '01019045','01019046') OR (UF.M_STATUS_CD IN ('01', '02') THEN 1 ELSE 0 END
But keep in mind that these are very different groupings.
精彩评论