Getting percentage of three columns

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

General SQL Server Forums

New to SQL Server Programming

Getting percentage of three columns

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-25 : 09:55:03

This isn't working what am I doing wrong. Getting incorrect syntax near *

select regionname, ((pend300 + pend351 + pend400) / * 100) as perc
from t16report

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-25 : 09:56:55

divided by what? you're missing something

((pend300 + pend351 + pend400) / 100.00)

Is that what you want?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 09:57:26

might be this? you're missing some operand in between too


select regionname, ((pend300 + pend351 + pend400) /(something missing here) * 100.0) as perc
from t16report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-25 : 10:02:04

Yes I was thanks!

Added this:

select reg, ((pend300 + pend351 + pend400) /(pendfo300) * 100.0) as perc
from t16report

Now I'm betting Divide by zero error encountered.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 10:08:18

thats because you've some rows with 0 in denominator


select reg, ((pend300 + pend351 + pend400) /(NULLIF(pendfo300,0)) * 100.0) as perc
from t16report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-25 : 10:24:21

Correct as usual

I changed the query to this:



select distinct dist, ((pendinfo) /(nullif(pendfo300, 0)) * 100.0) as perc
from t16report
where reg='c' and area='05'
order by dist

This works but displays as this. How do I get all of the 201, 210 etc... to add up.

Dist perc
201 NULL
201 3200.0
201 9400.0
210 NULL
210 600.0
210 800.0
210 2000.0
226 NULL
226 1300.0
226 2900.0
231 NULL
231 2100.0
305 NULL
305 1500.0
306 NULL
308 NULL
309 NULL
309 500.0
313 900.0
313 2900.0
313 3000.0

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-25 : 10:27:09

never mind I got this to work:


select distinct dist, sum((pendinfo) /(nullif(pendfo300, 0)) * 100.0) as perc
from t16report
where reg='c' and area='05'
group by dist
order by dist

dist perc
201 12600.0
210 3400.0
226 4200.0
231 2100.0
305 1500.0
306 NULL
308 NULL
309 500.0

How can I add 0 if there's NULL and get the numbers to format better?
313 6800.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 10:44:01

[code]
select distinct dist, isnull(sum((pendinfo) /(nullif(pendfo300, 0)) * 100.0),0) as perc
from t16report
where reg='c' and area='05'
group by dist
order by dist
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-25 : 10:51:02

Thanks again!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-25 : 11:05:21

I'm back...

Trying to get the numbers to come out as 12.6 etc...

I added this:


select distinct dist, isnull(sum((pendinfo) /(nullif(convert(decimal,pendfo300, 0)) * 100.0),0) as perc
from t16report
where reg='c' and area='05'
group by dist
order by dist

Error is:
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 11:43:40

try below


select distinct dist, isnull(
sum(
(pendinfo) /nullif((
convert(decimal(precisionvalue,scalevalue),pendfo300, 0)
 * 100.0
),0)),0) as perc
from t16report
where reg='c' and area='05'
group by dist
order by dist

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-25 : 12:13:32

Tried it but getting:

Incorrect syntax near ','.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-25 : 15:38:45

Figured it out...

Is there another way to do this statement instead of using the Case statement?


SELECT sort='000', RegionAcronym as Dist,''   AS reg, RegionAcronym, '' area, --'<a href=region.aspx?Reg=' + Reg + '>' + RegionAcronym + '</a>' as dist,
       SUM(totpenfodds)      AS 'totpenfodds', 
       SUM(pendinfo) AS 'pendinfo',
	   SUM(pendndds)       AS 'pendndds', 
	   SUM(pendfo300)      AS 'pendfo300', 
       SUM(penddds300)       AS 'penddds300', 

       SUM(pend250)  AS 'pend250',
		SUM(pend300) AS 'pend300',
		SUM(pend351)  AS 'pend351',
		SUM(pend400) AS 'pend400',
"perc" = 
Case
when sum(pendinfo) = 0 then 0
When sum(pendfo300) = 0 then 0
Else 
(convert(decimal(6,2),((((sum(pendfo300)) * 1.00)  /  sum(pendinfo)) * 100))) 
End

FROM   T16Report 
where reg='c'
group by reg, RegionAcronym

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-25 : 16:43:41

quote:
Originally posted by visakh16

thats because you've some rows with 0 in denominator

select reg, ((pend300 + pend351 + pend400) /(NULLIF(pendfo300,0)) * 100.0) as perc
from t16report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

That's not Correct

CASE WHEN pendfo300 = 0 THEN 0
WHEN pendfo300 IS NULL THEN 0
ELSE (pend300 + pend351 + pend400) /(pendfo300,0 * 100.0) END as perc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 16:45:27

why do you need case here? isnt below enough?

isnull((convert(decimal(6,2),((((sum(pendfo300)) * 1.00) / nullif(sum(pendinfo),0)) * 100))),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-26 : 07:51:44

Yes that's perfect. I didn't know how to set it up with the decimal so that's why I did it with the case statement. Your way of course is much easier. Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 08:41:23

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 08:46:17

quote:
Originally posted by X002548

quote:
Originally posted by visakh16

thats because you've some rows with 0 in denominator

select reg, ((pend300 + pend351 + pend400) /(NULLIF(pendfo300,0)) * 100.0) as perc
from t16report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

That's not Correct

CASE WHEN pendfo300 = 0 THEN 0
WHEN pendfo300 IS NULL THEN 0
ELSE (pend300 + pend351 + pend400) /(pendfo300,0 * 100.0) END as perc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

see modified suggestion
thats why i've that isnull() bit around this to make it 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-26 : 10:30:11

I hate non ANSI Stuff


DECLARE @t16report table (pendinfo int, pendfo300 int, dist char(1))

INSERT INTO @t16report (pendinfo, pendfo300, dist)
SELECT 10, 1, 'x' UNION ALL
SELECT 10, 2, 'x' UNION ALL
SELECT 10, 3, 'x' UNION ALL
SELECT 10, 4, 'y' UNION ALL
SELECT 10, 0, 'z'


select distinct dist, isnull(sum((pendinfo)
/ nullif((convert(decimal(15,4),pendfo300, 0)* 100.0),0)
),0) as perc
from @t16report
--where reg='c' and area='05', pendfo300
order by dist

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 10:37:47

quote:
Originally posted by X002548

I hate non ANSI Stuff

DECLARE @t16report table (pendinfo int, pendfo300 int, dist char(1))

INSERT INTO @t16report (pendinfo, pendfo300, dist)
SELECT 10, 1, 'x' UNION ALL
SELECT 10, 2, 'x' UNION ALL
SELECT 10, 3, 'x' UNION ALL
SELECT 10, 4, 'y' UNION ALL
SELECT 10, 0, 'z'

select distinct dist, isnull(sum((pendinfo)
/ nullif((convert(decimal(15,4),pendfo300, 0)* 100.0),0)
),0) as perc
from @t16report
--where reg='c' and area='05', pendfo300
order by dist

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

then use COALESCE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2012-01-26 : 14:24:57

Thanks again!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources