Dividing columns? How to? - 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 2005 Forums

Transact-SQL (2005)

Dividing columns? How to?

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 12:02:56

How can you divide columns in order to get a percentage from them? What I want to do is this:

I want a list of countries and cities where the city population is greater than 10% of the country population.

Here is what i have so far:


SELECT       c.Name as "Country Name"
             ,ci.Name AS "City Name"
             ,c.Population AS "Country Population"
             ,ci.Population AS "City Population"
FROM         Country c
INNER JOIN   City ci
ON           c.Code = ci.CountryCode

GROUP BY c.name, ci.name

I need to incorporate this now
(ci.Population ÷ c.Population) × 100 = percentage

Then I guess use a 'having > 10'

Can anyone help?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 12:09:57

[code]SELECT * FROM
(
SELECT c.Name as "Country Name"
,ci.Name AS "City Name"
,c.Population AS "Country Population"
,ci.Population AS "City Population"
,(ci.Population * 100.0/ c.Population) AS percentagepopulation
FROM Country c
INNER JOIN City ci
ON c.Code = ci.CountryCode
)t
WHERE t.percentagepopulation>10.0[/code]

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 12:17:51

That works perfect. you used 't' as a virtual table to hold the 'percentagepopulation' right? that is why you selected all from 't'. right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 12:21:21

quote:
Originally posted by jgonzalez14

That works perfect. you used 't' as a virtual table to hold the 'percentagepopulation' right? that is why you selected all from 't'. right?

Yup exactly. t is called derived table. That was basically done to avoid repeating the calaculation for percentage. SInce aliases cant be directly used in where you need a derived table otherwise you have to repeat all calculation of percentage in where also like below

SELECT       c.Name as "Country Name"
             ,ci.Name AS "City Name"
             ,c.Population AS "Country Population"
             ,ci.Population AS "City Population"
             ,(ci.Population * 100.0/ c.Population) AS percentagepopulation
FROM         Country c
INNER JOIN   City ci
ON           c.Code = ci.CountryCode
WHERE (ci.Population * 100.0/ c.Population)>10.0

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2008-11-09 : 12:27:54

Thank you that explains alot. I am learning alot from this forum!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 12:28:41

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources