Group Clause in Common Table Expression

Question

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

from myTable

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,

Regards,

Sourises,

Answer 1

Try this:

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

Answer 2

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


My blog

Answer 3

Thanks a million for the information and help,

Regards,

Iccsi,