0

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.

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
LarsVegas
  • 65
  • 6
  • it is not clear what you want. Should tbl_matrix contain all unique combinations of id, zip from the tbl_list? – splash58 Sep 03 '15 at 10:24
  • Yes, the matrix should be filled with the sum of 'occurrence' of all unique combinations of ID and zip. But tbl_matrix already contains all possible IDs (first row) and zip codes (first column). The rest of tbl_matrix is empty. I hope this answered your question. – LarsVegas Sep 03 '15 at 10:31
  • then matrix has the field for `sum of 'occurrence'` ? – splash58 Sep 03 '15 at 10:34
  • may be, you show the structure of matrix? – splash58 Sep 03 '15 at 10:35
  • `select zip_code, id, sum(occurrence) from tbl_list group by zip_code, id` – splash58 Sep 03 '15 at 10:37

2 Answers2

0

If I understand correctly, what you are looking to do is create a dynamic pivot table using MySQL (dynamic because you don't want to define the columns manually, but rather have them generated from some other source, in this case the unique zip codes). See here.

Also note that in Excel it makes sense for the first row to be the unique ZIP codes, because you cannot change the column names (A, B, C etc.). In SQL, however, the column names can and should be the unique ZIP codes, so there is no need for a first row of ZIP codes.

Community
  • 1
  • 1
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
0

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

splash58
  • 26,043
  • 3
  • 22
  • 34
  • The OP probably wants dynamic columns; generating the same SQL using `GROUP_CONCAT`, as in [this](http://stackoverflow.com/a/26297463/111794) answer and executing the dynamic SQL. – Zev Spitz Sep 03 '15 at 12:00