Calculate percentage values - 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 2008 Forums

Transact-SQL (2008)

Calculate percentage values

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-03-03 : 03:11:47

Hello all,
having 2 simple tables that shows me Users and City, like these:

Users
IDUser - IDCity
1 - 10
2 - 12
3 - 10
4 - 11
5 - 12
6 - 10
7 - 10
...

Cities
IDCity - CityName
10 - CityTen
11 - CityEleven
12 - CityTwelve
...

how can I calculate the percentage of presence of each city in the Users table?

I'd like to obtain something like:

CityName - Percentage
CityTen - 57%
CityEleven - 14%
CityTwelve - 29%

How can I obtain this?

Thanks a lot.

Luigi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-03 : 09:54:42

You can do this a few different ways - here is one:

SELECT DISTINCT
	c.CityName,
	COUNT(IDUsers) OVER (PARTITION BY c.IDCity)*100.0/COUNT(IDUsers) OVER()
FROM
	Cities c
	LEFT JOIN Users u ON u.IDCity = c.IDCity

I am implicitly assuming that the City names are unique. If not, you will need to do distinct on IDCity.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-03-03 : 15:14:45

Perfect, thank you very much Sunita.

L

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources