Nested SQL result, or something else?

Anon

I want to do the following, which doesn't work. I'm thinking there has to be a better way of doing this, as I'm fairly certain the code I have now is horrible. Basically what I need to do is fetch the number of killed for each event that is in the database. (event being Type). So you might have something like
Earthquake 51
Earthquake 40
Earthquake 100
Flood 100
Flood 2
Drought 40

etc. I need to go through the database and select all the events, and then total the number of killed for each event. The thing is, the event type is updateable, so I can't hard code the different types of events..I need to draw them from the database. The following code doesn't work, but each for loop works by itself. Any suggestions? (any way to optimize?) Maybe I need to get myself a good SQL book, heh.
<?
$querytype = "SELECT DISTINCT Type FROM data WHERE country LIKE '$country'";
$querykilled = "SELECT killed FROM data WHERE country LIKE '$country' AND Type LIKE '$type'";
$resulttype = mysql_query($querytype,$linkID);
$resultkilled = mysql_query($querykilled,$linkID);
for ($x=0; $x < mysql_num_rows($resulttype); $x++)
{
$row = mysql_fetch_assoc($resulttype);
$type = $row[Type];
for ($counter = 0; $row2 < mysql_num_rows($resultkilled); ++$counter)
{
foreach ($row2 as $value)
{
$sum += $value;
}
}
echo "Total Killed: $sum, Event $type";
echo "<br>";
}
?>

Anon

MySQL has a 'sum' function that allows you to do all that in one query. For example:

select distinct t1.type, sum(t2.killed) from data t1, data t2 where t1.type = t2.type

Or something like that.

Anon

hmmmm, I don't think that will work, i'm only using one table. And actually, Perhaps I should have said more about how the db is set up, and how I want it to work (the code I have above is just test code really) The DB is set up something like this

Country, Type, Year,Killed, Injured, Homeless

example data:
Afghanistan, Drought, 1954, 51, 0, 0
and so on

so now I need to retrieve all that data. And I want the layout to be like this

Type : Total Number of Events, #Killed, #Injured, #Homeless

so if you have 21 droughts listed under Afghanistan, and say 20 Earthquakes something like this

Flood: 21, 1000, 30, 10
Earthquake: 20, 100, 20, 200

and so on for each even type that is listed in the database. Is there some effecient wy of doing this, or Am i going to have to make a bunch of different queries?

Anon

one of these for each country ought to do the trick:

"SELECT sum(killed), sum(events), sum(injured), sum(homeless) from data where country='$country' group by type"

Anon

oh! and you'll probably need aliases for each. like this:

"SELECT sum(killed) as totalKilled, sum(events) as totalEvents, sum(injured) as totalInjured, sum(homeless) as totalHomeless from data where country='$country' group by type"