I need help on my computer science project! SQL related. :)

[alrwebber]

Below is a partial solution to the challenge to write an SQL query to find to list the US cities that have the highest percentage of Filipinos.

 

 

This will just list the number of people (not the percentage).

 

How do I adapat the fragment code above to show the ff:

a) Two columns - the Filipino Population and the Total Population

b) The Filipino Population as a percentage of the total population

 

Thank you to those who will help

[BaderEx]

Here you go:

 

This query will show the highest 3 cities of Filipinos.

 

num = is the number of Filipinos in a city

pop = is the population

 

SELECT city, num, pop, num/pop, (num/pop)*100 as precentage FROM `cit` order by num/pop DESC LIMIT 3

 

Good Luck

[abdfahim]

you need to paste your table structure for that

[kickstart]

Hi

 

I assume the table POPDATA has one row per member of the population (hence why using COUNT).

 

If so then something like this (not test so excuse any typos).

 

SELECT a.CITYNUM, CityPop, CityFilipinoPop, ((CityFilipinoPop/CityPop)*100) AS CityFilipinoPerc
FROM (SELECT POPDATA.CITYNUM, COUNT(*) AS CityPop
FROM POPDATA
GROUP BY POPDATA.CITYNUM) a
LEFT OUTER JOIN (SELECT POPDATA.CITYNUM, COUNT(*) AS CityFilipinoPop
FROM POPDATA
WHERE POPDATA.ANCNUMBER = 720
GROUP BY POPDATA.CITYNUM) b
ON a.CITYNUM = b.CITYNUM
ORDER BY CityFilipinoPerc

 

All the best

 

Keith