Monday, December 03, 2012 8:17 PM
I have GROUP BY clause in Common Table Expression like folowing
with CTEtable(MyID, MyName, MyValue1, MyValue2)
(Select MyID, MyName,
Count(MyV1) as MyValue1,
case when MyName = 'MyName' then count(MyV1) end as MyValue2
Group by MyID, MyName
I always get same value of MyValue1 and MyValues2 in CTETable. I see informaion from MSDN that Group Clause does not support is CTE recursive.
It does not give me error message , but it seems that it gives me not right result.
I just wonder can I use Group By clause in CTE like above.
Your help and information is great appreciated,
Monday, December 03, 2012 9:15 PM
with CTEtable(MyID, MyName, MyValue1, MyValue2) (Select MyID, MyName, Count(MyV1) as MyValue1, sum(case when MyName = 'MyName' then 1 else 0 end) as MyValue2 from myTable Group by MyID, MyName )
"Premature optimization is the root of all evil." - Knuth
If I provoked thought, please click the green arrow
If I provoked Aha! please click Propose as Answer
- Marked As Answer by sourises Tuesday, December 04, 2012 3:03 PM
Monday, December 03, 2012 9:23 PMModerator
Can you post some input data and desired output based on that input?
If you change your count expression to
COUNT(case when MyName = 'MyName' then 1 end) as MyValue2
you will still get 0 for most of the values and 1 for MyName = 'MyName' as you're grouping by MyName column.
If you remove that column from the list of columns and GROUP BY, then you'll get different values for MyValue1 and MyValue2
For every expert, there is an equal and opposite expert. - Becker's Law
Tuesday, December 04, 2012 3:04 PM
Thanks a million for the information and help,