-2

So I have a table weather that looks something like this: 

country | occurences 
USA     |  5 
UK      |  1 
BOL     |  1
USA     |  2 
UK      |  1

and this table weatherOccurences is what I want to generate: 

country | occurenceTotal
USA     | 7 
UK      | 2
BOL     | 1

Basically, for every distinct country I want to calculate all the occurences for it... but I'm not sure how to do that. I tried: 

INSERT INTO weatherOccurences(`occurenceTotal`)
SELECT SUM(occurences) from weather w1 
JOIN weatherOccurences w2 
ON w1.country = w2.country

However, instead of giving me the occurence count of each country, it just took a sum of all the occurences ever... and appended it to a new row at the end. How would I do the former? Any help would be greatly appreciated, thanks!

[edit] would I have to use UPDATE and SET for this?

ocean800
  • 3,489
  • 13
  • 41
  • 73
  • So you've got keywords like SUM, COLUMN, and MYSQL. I would have thought that would be enough to divine an answer without our assistance. – Strawberry Apr 01 '16 at 22:10
  • Possible duplicate of [MySql sum elements of a column](http://stackoverflow.com/questions/4586040/mysql-sum-elements-of-a-column) – Reto Apr 01 '16 at 22:14
  • 1
    The only thing you seem to have missed is the **`GROUP BY`** clause. Add `GROUP BY country` to the end of your query, and add the `country` column to the SELECT list (and to the list of columns in the INSERT). – spencer7593 Apr 01 '16 at 22:21

1 Answers1

3

You need to group by country, insert that country and select that country:

INSERT INTO weatherOccurences(country, occurenceTotal)
SELECT   country, SUM(occurences) 
from     weather
GROUP BY country
trincot
  • 317,000
  • 35
  • 244
  • 286