I have a table which contains the names of a country, the population of the country and the countries GDP, how would I display the names of the country and their per capita GDP
+12
A:
SELECT name, gdp/NullIf(population,0) AS PerCapitaGDP FROM "MyCountryTable"
Joel Coehoorn
2009-11-13 14:40:35
In the unlikely case that GDP is integral (not a decimal or float) and you're interested in precision to fractional values, remember to cast one the values (probably GDP) to a decimal or float before dividing)
Charles Bretana
2009-11-13 14:44:37
I would add a "where population > 0" to avoid any possibility of division by zero as well.
Otávio Décio
2009-11-13 14:47:15
@ocdecio: good point, though that would be a weird situation... I doubt there are any countries with a 0 population, and if you don't know the population a reasonable schema would represent that with NULL, which wouldn't cause an error here.
Joel Coehoorn
2009-11-13 14:56:04
@Joel - if a column *can* contain the value 0, it will sooner or later. Been burned before... :)
Otávio Décio
2009-11-13 15:03:03
Okay, okay, I know. But I don't like the "where population > 0" either, as it could exclude that country when you wanted to show it. Fixed it in a way that keeps those.
Joel Coehoorn
2009-11-13 15:42:15
+6
A:
SQL allows calculations to be performed inline, like:
SELECT Name, GDP / Population AS [Per Capita GDP] FROM YourTable
Rowland Shaw
2009-11-13 14:41:23
A:
To deal with the 0 population division by zero, I'd suggest using a CASE statement
SELECT
Name,
CASE IsNull(population,0)
WHEN 0
THEN 0
ELSE
THEN gdp/population
END AS 'PerCapitaGDP'
FROM Countries
This will work on MS SQL Server, you may need to look up syntax for which DBMS you are using (for example, it looks like MySQL uses END CASE instead of just END)
ManiacZX
2009-11-13 16:36:27