MySQL: Matching/filling a matrix with a list of ZIP Codes and IDs

Question

I'm trying to fill a table tbl_matrix. The first row contains unique IDs. The first column contains unique zip codes (5 integers).

The other table tbl_list contains three columns: 'zip_code', 'id', 'occurrence' (approx. 300000 rows).

Now I want to update/fill tbl_matrix with an algorithm/loop that checks if tbl_list contains the combination of a zip code and ID and returns the occurrence to tbl_matrix. In some cases the list contains two or more same combinations of zip_code and ID, but with different occurrence counts. In this case, the occurrences should be added up.

I'm using MySQL Workbench on a Windows machine and I'm not experienced with Perl/Python/PHP, so it would be great to solve the problem solely in MySQL.

In Excel I would use a vlookup function per column in tbl_matrix, but until now I couldn't adapt other similar (My)SQL examples from stackoverflow. It would be great if you could help me (its a college project). Thank you so much, Lars.

Answer 1

i think this can be an aproach to solve the problem

create table thetable (id int, zip_code int,  occurrence int);
insert into thetable values (1,1,1), (2,1,2), (1,2,3), (1,2,4);

select id, 
       sum(if(zip_code=1, sum, 0)) zip1,
       sum(if(zip_code=2, sum, 0)) zip2
     from 
       (select id, zip_code, sum(occurrence) sum 
           from tbl_list  
         group by id, zip_code
       ) t
     group by id

result

| id | zip1 | zip2 |
|----|------|------|
|  1 |    1 |    7 |
|  2 |    2 |    0 |

Demo on sqlfiddle