June 26, 2008 at 12:43 am
Hi again, another day yet another problem...
I'm trying to get my qeury below to count specific data which was created in a specific way (labeled as true or false in the column created_as) but instead of showing this as just true or false, I'd like it to write for example 'A' if false and 'B' if true. Is there any way of doing this smoothly? :S
Script:
select datepart(ww, created_date) as week, count(*) as count, created_as as type from example
where (year(created_date) = 2008)
group by datepart(ww, created_date), created_as
order by created_as, datepart(ww, created_date)
Ie.
It's giving me something like:
week count type
1 239 false
2 499 false
. . .
. . .
. . .
26 236 false
1 99 true
2 135 true
etc.
but I want the falses and trues changed to strings of my choice...
Thankful for help as always
Jonatan
June 26, 2008 at 12:48 am
Have you seen the case statement before?
select datepart(ww, created_date) as week, count(*) as count,
CASE created_as
WHEN 'true' THEN 'Some String Here'
WHEN false THEN 'Some Other String'
ELSE 'default case'
END as type
from example
where (year(created_date) = 2008)
group by datepart(ww, created_date), CASE created_as
WHEN 'true' THEN 'Some String Here'
WHEN false THEN 'Some Other String'
ELSE 'default case'
END
order by type, datepart(ww, created_date)
Should be more of less what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2008 at 12:56 am
Thanks for the fast reply!
I did try case before, but wasn't really sure about the correct syntax and even now I get an error when I try it:
Column "example.created_as" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
EDIT: never mind, I got it to work, I removed the case statement in the group by and then it worked fine 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply