Calculating Averages - 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

Calculating Averages

Author

Topic

Johnathan
Starting Member

30 Posts

Posted - 2011-01-20 : 10:17:48

Hi all,

I'm looking for a little support in calculating percentages in SQL Server.

I am trying to display a property list of Average Days on Market per branch. I currently have code that displays all sold properties for a given month, and shows each properties 'Date on Market' and 'Date Sold'

I need to group this information by branch, with a further column showing the 'Average Days on Market' for each branch respectively

I'm assuming this will include doing a subtraction of 'date on market' from 'date sold', and then producing an average of this result and grouping per branch.

Here Is my current code:

select pcode as PropertyCode,
	   phsename as HouseName,
	   phseno as HouseNumber,
	   paddress1 as Address1,
	   paddress2 as Address2,
	   paddress3 as Address3,
	   ppostcode as PostCode,
	   pforsdate as DateOnMarket,
	   pexchdate as DateSold,
	   as DaysOnMarket
	   
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,
	   pforsdate as DateOnMarket,
	   pexchdate as DateSold,
	   as DaysOnMarket

from proparch

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

Any help with this at all would be greatly appreciated.
Thanks so much.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-20 : 10:28:44

select branch, avg(datediff(dd,pforsdate,pexchdate))
from proparch
group by branch

not sure where the branch is.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-20 : 10:48:03

Thanks for that!

Currently trying it but think I have syntax issues as I'm being given into trouble for the "syntax around GROUP"

What is 'dd' for? Didn't get that part totally.
Here's what I have tried now:

select pocode as Branch, 
	   avg(datediff(dd, pforsdate, pexchdate))
	   
from property
	   
where ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'


UNION ALL
 
(Select pocode as Branch, 
		avg(datediff(dd, pforsdate, pexchdate))

from proparch

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-20 : 10:53:29

select branch, avg(dys)
from
(
select pocode as Branch, dys = datediff(dd, pforsdate, pexchdate)
from property
where ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30'

UNION ALL

Select pocode as Branch, dys = datediff(dd, pforsdate, pexchdate)
from proparch
where ptype='S' and synchDel='0' and pexchdate between '2010-11-01' and '2010-11-30')
) a
group by Branch

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-20 : 11:56:22

Brilliant, works a treat. Thanks very much kind sir.

Can you give me a little bit of info on how that 'dd' works, and what the small 'a' is for just before group by.

Thanks again.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-20 : 12:08:26

The a is an alias name for the derived table. Doesn't do anything in this case - just needs to be there.

dd is the qualifier for the datediff - it means the number of day boundaries (midnights) between the two dates.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Johnathan
Starting Member

30 Posts

Posted - 2011-01-21 : 05:04:08

ahh I see. Perfect.

Thanks a bunch!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources