This code works perfectly but probably due to the way i asked my question earlier. I have provided more information below:
Example Output:
'Central Africa' 23336000
means that in the Central Africa region, a total of 23336000 people live in countries with higher-than-average life expectancy for that region
This is obtained as follows:
the average expectancy in the region is 50.21 years (sic), and the following countries have higher life expectancy:
"Cameroon",54.8,15085000
"Equatorial Guinea",53.6,453000
"Sao Tome and Principe",65.3,147000
"Chad",50.5,7651000
which give a tot pop. of 23336000
The SQL I uploaded into mysql workbench is here:
http://www.filedropper.com/worldinnodb_3
DROP TABLE country;
CREATE TABLE country(
Code VARCHAR(3) NOT NULL PRIMARY KEY
,Name VARCHAR(44) NOT NULL
,Continent VARCHAR(13) NOT NULL
,Region VARCHAR(25) NOT NULL
,SurfaceArea NUMERIC(10,1) NOT NULL
,IndepYear INTEGER NOT NULL
,Population INTEGER NOT NULL
,LifeExpectancy NUMERIC(4,1) NOT NULL
,GNP NUMERIC(9,1) NOT NULL
,GNPOld NUMERIC(9,1) NOT NULL
,LocalName VARCHAR(44) NOT NULL
,GovernmentForm VARCHAR(44) NOT NULL
,HeadOfState VARCHAR(36)
,Capital INTEGER NOT NULL
,Code2 VARCHAR(2) NOT NULL
);
with some few values
INSERT INTO mytable(Code,Name,Continent,Region,SurfaceArea,IndepYear,Population,LifeExpectancy,GNP,GNPOld,LocalName,GovernmentForm,HeadOfState,Capital,Code2) VALUES ('ABW','Aruba','North America','Caribbean',193.0,0,103000,78.4,828.0,793.0,'Aruba','Nonmetropolitan Territory of The Netherlands','Beatrix',129,'AW');
INSERT INTO mytable(Code,Name,Continent,Region,SurfaceArea,IndepYear,Population,LifeExpectancy,GNP,GNPOld,LocalName,GovernmentForm,HeadOfState,Capital,Code2) VALUES ('AFG','Afghanistan','Asia','Southern and Central Asia',652090.0,1919,22720000,45.9,5976.0,0.0,'Afganistan/Afqanestan','Islamic Emirate','Mohammad Omar',1,'AF');
INSERT INTO mytable(Code,Name,Continent,Region,SurfaceArea,IndepYear,Population,LifeExpectancy,GNP,GNPOld,LocalName,GovernmentForm,HeadOfState,Capital,Code2) VALUES ('AGO','Angola','Africa','Central Africa',1246700.0,1975,12878000,38.3,6648.0,7984.0,'Angola','Republic','José Eduardo dos Santos',56,'AO');
for each world region, I want a count of the total population of all countries in that region, where the life expectancy is larger than the average life expectancy for their region ?