Hi everyone,
I have a count expression within a Case When statement but I need to add a expression to it.
Please see below:
case
when [STATE].[TYPE_CD] = '7' then count([STATE].[STATE_DATA_REC_ID]) else 0
end
Now, I need to add the below to the above expression:
case
when [STATE].[TYPE_CD] = '7' then count([STATE].[STATE_DATA_REC_ID] FOR ONLY STATE='7') else 0
end
This way, I'm making sure the COUNT is for only STATE = 7
Please help if you can.
thx so much!! :) 8)
is state = 7 equivalent to [state].[type_cd] = 7?
if so, better create another query filtered by type_cd = 7. then join back to your main query.
Quote from: gosoccer on 07 Nov 2015 02:01:21 PM
Hi everyone,
I have a count expression within a Case When statement but I need to add a expression to it.
Please see below:
case
when [STATE].[TYPE_CD] = '7' then count([STATE].[STATE_DATA_REC_ID]) else 0
end
Now, I need to add the below to the above expression:
case
when [STATE].[TYPE_CD] = '7' then count([STATE].[STATE_DATA_REC_ID] FOR ONLY STATE='7') else 0
end
This way, I'm making sure the COUNT is for only STATE = 7
Please help if you can.
thx so much!! :) 8)
Another approach is to create an expression that returns the ID for what you want to count and null for what you don't want to count and set the aggregate function for the data item to count.
case
when [STATE].[TYPE_CD] = '7'
then [STATE].[STATE_DATA_REC_ID]
else null
end
Thanks Khyman and Lynn,
Lynn,
I tried your approach first and it worked perfectly. I want to thank you
so much for your time. It means a lot to me. I identified a way last week
but the counts were coming very wrong. :D :) ;)
Quote from: gosoccer on 09 Nov 2015 07:30:51 AM
Thanks Khyman and Lynn,
Lynn,
I tried your approach first and it worked perfectly. I want to thank you
so much for your time. It means a lot to me. I identified a way last week
but the counts were coming very wrong. :D :) ;)
You are most welcome. I'm glad it worked out. :)
For future reference, you could also embed the case statement INSIDE the count function. I generally only do this if I need to specify the scope with a "for" clause. In that case the aggregate function of the query item would be automatic or possibly total (can't remember exactly what works best). This is sort of what you were attempting at first so thought you should know you were certainly on the right track in your thought process, but unless you need the "for" clause I think the first approach is easier.
count(
case
when [STATE].[TYPE_CD] = '7'
then [STATE].[STATE_DATA_REC_ID]
else null
end
for [some other thing in your query]
)
Yeah! That looks like a really nice approach as well. I'll make a note of it.
If I ever can be a help, please let me know.
Thx,