Help with sql statement please

Anon

I am using the following SQL statement on a MySQL db..

SELECT country,DisType, DisSubset, DisName, COUNT(DisType) as totalEvents, ROUND(AVG(killed),1) as averageKilled, ROUND(AVG(injured),1) as averageInjured, ROUND(AVG(homeless),1) as averageHomeless, ROUND(AVG(Affected),1) as averageAffected, ROUND(AVG(TotAft),1) as averageTotAft, ROUND(AVG(DamageUS),1) as averageDamageUS from emdat where DisType LIKE 'Drought' group by country order by country ASC

and I also use a statement much like the one above, except with the added AND clause "AND year BETWEEN 1982 AND 1983" ,

Now, I want to compare to two statements...however, I am running into a problem where lets say Afghanistan does not have any Drought events between 1982 and 1983, but it does have Drought events outside of those years. So in my first SQL statement, I don't want Afghanistan listed at all.

what do i need to add to make that work?

[deleted]

First of all, your query is not valid.
It is only MySQL's crap GROUP BY that allows this query to run.

You select non-aggregates that are not in the GROUP BY clause. That means MySQL is giving you random values for those fields.

Your GROUP BY only contains 'country', which means that 'country' is the only field you can select. All the others must be COUNT(), SUM(), MIN/MAX or some other aggregate.

Second, look at the CASE statement, that will let you select values based on other values;

SELECT SUM(CASE WHEN year BETWEEN this AND that THEN 1 ELSE 0 END) AS droughts_inside_years,
SUM(CASE WHEN year BETWEEN this AND that THEN 0 ELSE 1 END) AS droughts_outside_years

then forget about the YEAR in the WHERE statement and you'll get the data in one query.

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

I'm not sure I understand, because if I run queries on the mysql db (command line) i do get the correct values...my only problem is getting the Historical Averages....see below..lets say i print out something like this

Algeria
2.00 0.00 0.00 0.00 0.00 0.00 0.00

Hist. ave. per year
0.20 0.00 0.00 0.00 716,600.00 716,600.00 6,040.00

the numbers for Algeria are fine, those numbers are coming from the years 1982-1983, then I want to get the Historical Averages (between the years 1975-2000 (those are the year ranges in the db), but that's where I'm running into problems, because those numbers are wrong...those numbers are actually the correct numbers, but for Afghanistan, and not Algeria...because there are no Droughts in Afganistan during 1982-1983....

so GROUP BY shouldn't be used? I can actually get rid of the country,DisType, DisSubset, DisName (non-aggregates) and have the query still work.

[deleted]

"because there are no Droughts in Afganistan during 1982-1983...."

then why does the query return any data at all, if there WHERE clearly states that you only want data for items that have droughts?

I suggest you 'start over', by goig back to a very simple query to see if you get the correct records without the group by etc.

You can use GROUP BY, but it's not 'legal' to select non-aggregates that are not mentioned in the GROUP BY clause. If you do, MySQL will return 'random' data from any of the records that matched the WHERE clause.