Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mikejohnson
Posting Yak Master
153 Posts |
 Posted - 2004-06-01 : 10:26:18
|
| if this is the field i'm calculating, how do i return it as a decimal with 2 decimal places and place a % sign in front of it? i tried what i thought it would be and returned only integers with no decimals.case when SUM(CASE WHEN blnComplete = 1 THEN 1 ELSE 0 END)=0 then 0 else SUM(CASE WHEN blnComplete = 1 THEN 1 ELSE 0 END)*100/COUNT(*) end as [% of Hits Completed]my query goes something like this:select case when SUM(CASE WHEN blnComplete = 1 THEN 1 ELSE 0 END)=0 then 0 else SUM(CASE WHEN blnComplete = 1 THEN 1 ELSE 0 END)*100/COUNT(*) end as [% of Hits Completed] from table |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-06-01 : 10:31:29
|
| You need to cast it as numeric (or decimal) with a scale of 2 and then cast it to varchar, so you can stick a % after it. Neat trick: multiply by 100.00 to automatically convert the expression to a numeric of scale 2.SELECT CAST((SUM(CASE WHEN blnComplete = 1 THEN 1 ELSE 0 END)*100.00/COUNT(*)) as varchar(10)) + '%'OS |
 |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-06-01 : 10:37:35
|
| Try something like this:select case when SUM(CASE WHEN blnComplete = 1 THEN 1 ELSE 0 END)=0 then '% ' + LTRIM(STR(CAST(0 as DECIMAL(18, 2)), 18)) else '% ' + LTRIM(STR(CAST(SUM(CASE WHEN blnComplete = 1 THEN 1 ELSE 0 END)*100/COUNT(*) as DECIMAL(18, 2)), 18)) end as [% of Hits Completed] from mytableDuane. |
|
|
|
|
|
|
query help - decimals, strings....