[SOLVED] SELECT SUM WHEN id=id

[DEVILofDARKNESS]

I have a query which runs fine and outputs:

 

NATION_NAME      POWER

The Belgian Union    6

The Belgian Union    4

Ireland                  2

 

Now I want to show it DISTINCT, but the powers should be count up so I would get:

 

NAT._NAME    POWER

The Belg. UN.    10

Ireland              2

 

how can I solve this?

 

SELECT nations.nation_name, regions.region_id, (SELECT SUM( weapons.defense) * region_weapons.ammount) As power, (SELECT SUM( power ) WHERE regions.nation_id) As ultimatepower FROM weapons INNER JOIN region_weapons ON region_weapons.weapon_id = weapons.weapon_id INNER JOIN regions ON regions.region_id = region_weapons.region_id INNER JOIN nations ON nations.nation_id = regions.nation_id GROUP BY regions.region_id

[avvllvva]

try something like this

 

SELECT NATION_NAME,SUM(POWER) FROM tbl_name group by NATION_NAME