% of two sum case statements - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

% of two sum case statements

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-10-03 : 09:29:47

Hi, I need help.

I need to get the % of two case statement values but no luck.
Case1:
sum(case when rtrim(Doc_Type) = 'Invoice' then 1 else 0 end) as Inv

Case2:
sum(case when rtrim(Doc_Type) <> 'Invoice' then 1 else 0 end) as Cr

Result i want is Cr % of Inv

My query to get %:
,case when sum(case when rtrim(Doc_Type) = 'Invoice' then 1 else 0 end) = 0
then 0
else
sum(case when rtrim(Doc_Type) <> 'Invoice' then 1 else 0 end)/sum(case when rtrim(Doc_Type) = 'Invoice' then 1 else 0 end)*100
end Perc

but i get: Inv - 100
Cr - 20
Perc - 0

I tried putting it in a subquery but the result does not match
,case when Inv = 0
then 0
else CR/Inv
end as Perc

but still does not work

Any help Please, Regards

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-03 : 09:36:57

the problem is the decimal point in your calculation.
just try...

select 20/100

...you'll see it's 0. if you cast them as float before the division it's fine

select cast(20 as float)/cast(100 as float)*100

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 09:45:21

[code]SELECT Col1,
Inv,
Cr,
100.0 * Cr / Inv
FROM (
SELECT Col1,
SUM(CASE WHEN RTRIM(Doc_Type) = 'Invoice' THEN 1 ELSE 0 END) AS Inv,
SUM(CASE WHEN RTRIM(Doc_Type) <> 'Invoice' THEN 1 ELSE 0 END) AS Cr
FROM Table1
GROUP BY Col1
) AS k[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-03 : 09:47:07

or multiply the expression by 1.0

,case when sum(case when rtrim(Doc_Type) = 'Invoice' then 1 else 0 end) = 0
then 0
else
1.0*sum(case when rtrim(Doc_Type) <> 'Invoice' then 1 else 0 end)/sum(case when rtrim(Doc_Type) = 'Invoice' then 1 else 0 end)*100
end Perc

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-10-03 : 09:57:38

Hi, A Big Thank You as the Cast as Float works great.
Thank You

quote:
Originally posted by elancaster

the problem is the decimal point in your calculation.
just try...

select 20/100

...you'll see it's 0. if you cast them as float before the division it's fine

select cast(20 as float)/cast(100 as float)*100

Em

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2007-10-03 : 10:00:51

Thank You as well to Peter Larsson & Madhivanan, I really appreciate the help.
I used the cast as float.
Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources