SQL percentages in SQL Server - 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

General SQL Server Forums

New to SQL Server Programming

SQL percentages in SQL Server

Author

Topic

Johnathan
Starting Member

30 Posts

Posted - 2011-01-10 : 10:21:26

Hi all,

I need help with SQL Server. I'm currently working a reporting tool that can produce information on the percentage of acheived price against valuation price.

We have several branches and the results need to be shown as a percentage per branch which is causing difficulty. The code I have is the following

select pcode as PropertyCode,
	   phsename as HouseName,
	   phseno as HouseNumber,
	   paddress1 as Address1,
	   paddress2 as Address2,
	   paddress3 as Address3,
	   ppostcode as PostCode,
	   pvalprice as Valuation,
	   pexchprice as AchievedPrice

	   
from property
	   
where ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'


UNION ALL

 
(select pcode as PropertyCode,
	   phsename as HouseName,
	   phseno as HouseNumber,
	   paddress1 as Address1,
	   paddress2 as Address2,
	   paddress3 as Address3,
	   ppostcode as PostCode,
	   pvalprice as Valuation,
	   pexchprice as AchievedPrice




from proparch

where ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30')
group by pcode

Any help here at all will be greatly appreciated.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 10:40:45

i didnt understand the need of group by pcode at bottom. for getting percentage its just a matter of adding an expression like pexchprice*100.0/pvalprice to both select lists

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-10 : 10:41:42

Which table do you need the percentage from (property or proparch)?

Why are you putting them together as a UNION? do you need to return the combined results from each table?

Poor planning on your part does not constitute an emergency on my part.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-10 : 10:51:30

Thanks,

Sorry the group by part does not need to be there. I was just trying something with that and didn't remove it.

I had them both joined together as a UNION as I was using this query originaly to show the monthly solds which needed to use both property table and archived property table. So yes I do need the result to be a combination of both tables.

It's basically a percentage per branch, of sale prices against valuation prices. Therefore my end result would ideally by 16 rows long, with the last value being the percentage of achieved properties against valuation prices.

Thanks again

Johnathan
Starting Member

30 Posts

Posted - 2011-01-10 : 11:01:35

Is something like this a little closer?

select pcode as PropertyCode,
	   phsename as HouseName,
	   phseno as HouseNumber,
	   paddress1 as Address1,
	   paddress2 as Address2,
	   paddress3 as Address3,
	   ppostcode as PostCode,
	   pvalprice as Valuation,
	   pexchprice as AchievedPrice,
	   if (pvalprice = '0') begin notapp as perc end
	   else convert(varchar, (pexchprice * 100.0) / pvalprice, 5) as perc

	   
from property
	   
where ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'


UNION ALL

 
(select pcode as PropertyCode,
	   phsename as HouseName,
	   phseno as HouseNumber,
	   paddress1 as Address1,
	   paddress2 as Address2,
	   paddress3 as Address3,
	   ppostcode as PostCode,
	   pvalprice as Valuation,
	   pexchprice as AchievedPrice,
	   if (pvalprice = '0') begin notapp as perc end
	   else convert(varchar, (pexchprice * 100.0) / pvalprice, 5) as perc



from proparch

where ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:04:03

yup. a little difference


select pcode as PropertyCode,
	   phsename as HouseName,
	   phseno as HouseNumber,
	   paddress1 as Address1,
	   paddress2 as Address2,
	   paddress3 as Address3,
	   ppostcode as PostCode,
	   pvalprice as Valuation,
	   pexchprice as AchievedPrice,
	   pexchprice * 100.0 / nullif(pvalprice,0) as perc

	   
from property
	   
where ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'


UNION ALL

 
(select pcode as PropertyCode,
	   phsename as HouseName,
	   phseno as HouseNumber,
	   paddress1 as Address1,
	   paddress2 as Address2,
	   paddress3 as Address3,
	   ppostcode as PostCode,
	   pvalprice as Valuation,
	   pexchprice as AchievedPrice,
	   pexchprice * 100.0 / nullif(pvalprice,0) as perc



from proparch

where ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30')

and do that not applicable setting at front end by checking for NULLs

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

Johnathan
Starting Member

30 Posts

Posted - 2011-01-10 : 11:19:23

Brilliant. Thanks mate. Seems to work well.

Although I now need to get this so it's shown as a single percentage per branch. (hence why I was playing around with group by pcode)

There is also pocode which represents the branch. will GROUP BY branch work? If I was to add a sum of all valuations and exch prices.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:22:51

group by pcode will work as long as you apply sum on all the other columns.

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

Johnathan
Starting Member

30 Posts

Posted - 2011-01-10 : 11:59:16

Yeah that's what I thought.

I'm currently trying something like the following with less luck than I hoped!

SELECT pocode AS Branch
	   sum (pvalprice AS Valuation,
       pexchprice AS AchievedPrice)
	   pexchprice * 100.0 / nullif(pvalprice,0) AS perc


	   
FROM property
	   
WHERE ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'


UNION ALL

 
(SELECT pocode AS Branch
	   sum (pvalprice AS Valuation,
       pexchprice AS AchievedPrice)
	   pexchprice * 100.0 / nullif(pvalprice,0) AS perc


FROM proparch

WHERE ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30')
GROUP BY Branch

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 12:02:55

try


SELECT Branch,
SUM(Valuation) AS Valuation,
SUM(AchievedPrice) AS AchievedPrice,
SUM(AchievedPrice) * 100.0/SUM(Valuation) AS perc
FROM
(
SELECT pocode AS Branch
	   pvalprice AS Valuation,
       pexchprice AS AchievedPrice


	   
FROM property
	   
WHERE ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'


UNION ALL

 
SELECT pocode AS Branch
	   pvalprice AS Valuation,
       pexchprice AS AchievedPrice


FROM proparch

WHERE ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'
)t
GROUP BY Branch

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

Johnathan
Starting Member

30 Posts

Posted - 2011-01-11 : 05:02:50

Thanks again.

This returns the following error.

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Is it possible to add incorporate the NULLIF command to correct this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 10:51:42

yup its possible.


SELECT Branch,
SUM(Valuation) AS Valuation,
SUM(AchievedPrice) AS AchievedPrice,
SUM(AchievedPrice) * 100.0/NULLIF(SUM(Valuation),0) AS perc
FROM
(
SELECT pocode AS Branch
	   pvalprice AS Valuation,
       pexchprice AS AchievedPrice


	   
FROM property
	   
WHERE ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'


UNION ALL

 
SELECT pocode AS Branch
	   pvalprice AS Valuation,
       pexchprice AS AchievedPrice


FROM proparch

WHERE ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'
)t
GROUP BY Branch

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

Johnathan
Starting Member

30 Posts

Posted - 2011-01-12 : 10:07:24

Thank you very much, works a treat.

That's one out of 4 down! I might be back with the next lot if I get stuck again!

Thanks again mate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 10:08:19

no probs...always welcome

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

Johnathan
Starting Member

30 Posts

Posted - 2011-01-13 : 05:48:43

Hi Again,

Ok, so I've been working on a couple of new reports quite similar to the previous in the sense of using percentages. However, I think I have something syntactically wrong as it's giving me problems.

The reports I am trying to achieve are:
Percentage of new properties against stock
Percentage of sold properties against stock

Code is as the following

SELECT Branch,
SUM(NewProps) AS NewProps,
SUM(Stock) AS Stock,
SUM(NewProps) * 100.0/SUM(Stock) AS Perc	
FROM
(
SELECT pocode AS Branch,
       (SELECT *	   
		FROM property	   
		WHERE ptype='S' and synchDel='0' and pforsdate between '2010-11-01' and '2010-11-30'

		UNION ALL
 

		FROM proparch
		WHERE ptype='S' and synchDel='0' and pforsdate between '2010-11-01' and '2010-11-30') 
		
		AS NewProps,
		--Query to get New Properties
		
		
		(SELECT *		
        FROM property         
        WHERE ptype='S' and pavailable='True' and synchDel='0')
        
        AS Stock
        --Query to get Current Stock		
)t
GROUP BY Branch

Looks like it can't be far off to me, but SQL Server doesn't like it. Any suggestions on why so?

Many thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 10:43:53

whats the purpose of that inner table query? you're not at all linking it to outer query

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

Johnathan
Starting Member

30 Posts

Posted - 2011-01-13 : 11:04:53

The outer query uses 'NewProps' and 'Stock'

The inner queries define 'NewProps' and 'Stock' so that they could hopefully be used in the outer query. Hope that makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 11:12:34

but there should be some relation among them rite?

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

Johnathan
Starting Member

30 Posts

Posted - 2011-01-13 : 11:25:03

Yes.

I thought there was to be honest, as the inital query (after the percentage line) has a FROM statement, so that this data will be populated from the following SELECT queries, which are NewProps and Stock.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-17 : 06:55:36

Are you suggesting that my inner query doesn't relate to the main query?

Thanks again,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-24 : 11:16:42

it is not. also its not syntactically correct also

i can see FROM after UNION ALL without a SELECT

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources