MySQL grouping view -
i have table recap this. table used recap data education in organization. using mysql 5
mysql> select * rekap_teams.tblrekappendidikan_divisitahunan; +----+------------------------------------+-------+----+-----+-----+----+----+----+-----+----+----+-------+ | id | divisi | year | sd | smp | sma | d1 | d2 | d3 | s1 | s2 | s3 | total | +----+------------------------------------+-------+----+-----+-----+----+----+----+-----+----+----+-------+ | 1 | corporate | 2014 | 0 | 0 | 0 | 0 | 0 | 0 | 25 | 4 | 0 | 29 | | 2 | corporate services | 2014 | 0 | 0 | 137 | 0 | 1 | 2 | 195 | 4 | 1 | 340 | | 3 | finance | 2014 | 0 | 0 | 0 | 0 | 1 | 2 | 54 | 2 | 0 | 59 | | 4 | sales 1 | 2014 | 0 | 0 | 0 | 0 | 0 | 0 | 19 | 0 | 0 | 19 | | 5 | sales 2 | 2014 | 0 | 0 | 1 | 0 | 0 | 0 | 9 | 2 | 0 | 12 | | 6 | promotion | 2014 | 0 | 0 | 0 | 0 | 1 | 0 | 25 | 1 | 0 | 27 | | 7 | non division - sales & marketing | 2014 | 0 | 0 | 0 | 0 | 0 | 0 | 66 | 1 | 0 | 67 | | 8 | non division - board of management | 2014 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 8 | 0 | 9 | | 9 | programming | 2014 | 0 | 0 | 0 | 0 | 1 | 0 | 28 | 3 | 0 | 32 | | 10 | operational broadcast | 2014 | 0 | 0 | 4 | 0 | 9 | 9 | 376 | 3 | 0 | 401 | | 11 | non division | 2014 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 12 | production 1 | 2014 | 0 | 0 | 1 | 0 | 1 | 2 | 124 | 1 | 0 | 129 | | 13 | production 2 | 2014 | 0 | 0 | 1 | 0 | 5 | 3 | 109 | 1 | 0 | 119 | | 14 | film, drama & sport | 2014 | 0 | 0 | 0 | 0 | 1 | 4 | 76 | 1 | 0 | 82 | | 15 | news | 2014 | 0 | 0 | 0 | 0 | 4 | 5 | 218 | 5 | 0 | 232 | | 16 | production facilities | 2014 | 0 | 0 | 2 | 0 | 4 | 13 | 597 | 5 | 0 | 621 | | 17 | production 4 | 2014 | 0 | 0 | 0 | 0 | 0 | 2 | 40 | 0 | 0 | 42 | | 18 | pcdc | 2014 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 5 | | 19 | corporate | 2015 | 0 | 0 | 0 | 0 | 0 | 0 | 26 | 3 | 0 | 29 | | 20 | corporate services | 2015 | 0 | 0 | 130 | 0 | 1 | 1 | 194 | 2 | 1 | 329 | | 21 | finance | 2015 | 0 | 0 | 0 | 0 | 1 | 1 | 49 | 2 | 0 | 53 | | 22 | sales 1 | 2015 | 0 | 0 | 0 | 0 | 0 | 0 | 18 | 0 | 0 | 18 | | 23 | sales 2 | 2015 | 0 | 0 | 1 | 0 | 0 | 0 | 9 | 2 | 0 | 12 | | 24 | promotion | 2015 | 0 | 0 | 0 | 0 | 1 | 0 | 25 | 1 | 0 | 27 | | 25 | non division - sales & marketing | 2015 | 0 | 0 | 0 | 0 | 0 | 0 | 64 | 1 | 0 | 65 | | 26 | non division - board of management | 2015 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 8 | 0 | 9 | | 27 | programming | 2015 | 0 | 0 | 0 | 0 | 1 | 0 | 27 | 3 | 0 | 31 | | 28 | operational broadcast | 2015 | 0 | 0 | 3 | 0 | 9 | 7 | 369 | 2 | 0 | 390 | | 29 | non division | 2015 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 30 | production 1 | 2015 | 0 | 0 | 1 | 0 | 1 | 2 | 117 | 1 | 0 | 122 | | 31 | production 2 | 2015 | 0 | 0 | 1 | 0 | 5 | 2 | 106 | 1 | 0 | 115 | | 32 | film, drama & sport | 2015 | 0 | 0 | 0 | 0 | 1 | 4 | 72 | 1 | 0 | 78 | | 33 | news | 2015 | 0 | 0 | 0 | 0 | 4 | 5 | 211 | 5 | 0 | 225 | | 34 | production facilities | 2015 | 0 | 0 | 2 | 0 | 2 | 6 | 583 | 5 | 0 | 598 | | 35 | production 4 | 2015 | 0 | 0 | 0 | 0 | 0 | 0 | 36 | 0 | 0 | 36 | | 36 | pcdc | 2015 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | 0 | 4 | +----+------------------------------------+-------+----+-----+-----+----+----+----+-----+----+----+-------+ 36 rows in set (0.00 sec) i alwasy try solve problem grouping query. stack, how query create view this?
+------------------------------------+----+-----+-----+----+----+----+-----+----+----+----+-----+-----+----+----+----+-----+----+----+ | divisi | 2014 | 2015 | + +----+-----+-----+----+----+----+-----+----+----+----+-----+-----+----+----+----+-----+----+----+ | | sd | smp | sma | d1 | d2 | d3 | s1 | s2 | s3 | sd | smp | sma | d1 | d2 | d3 | s1 | s2 | s3 | +------------------------------------+----+-----+-----+----+----+----+-----+----+----+----+-----+-----+----+----+----+-----+----+----+ | corporate | 0 | 0 | 0 | 0 | 0 | 0 | 25 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 25 | 4 | 0 | | corporate services | 0 | 0 | 137 | 0 | 1 | 2 | 195 | 4 | 1 | 0 | 0 | 137 | 0 | 1 | 2 | 195 | 4 | 1 | | film, drama & sport | 0 | 0 | 0 | 0 | 1 | 4 | 76 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 4 | 76 | 1 | 0 | | finance | 0 | 0 | 0 | 0 | 1 | 2 | 54 | 2 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 54 | 2 | 0 | | news | 0 | 0 | 0 | 0 | 4 | 5 | 218 | 5 | 0 | 0 | 0 | 0 | 0 | 4 | 5 | 218 | 5 | 0 | | non division | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | non division - board of management | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 8 | 0 | | non division - sales & marketing | 0 | 0 | 0 | 0 | 0 | 0 | 66 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 66 | 1 | 0 | | operational broadcast | 0 | 0 | 4 | 0 | 9 | 9 | 376 | 3 | 0 | 0 | 0 | 4 | 0 | 9 | 9 | 376 | 3 | 0 | | pcdc | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | | production 1 | 0 | 0 | 1 | 0 | 1 | 2 | 124 | 1 | 0 | 0 | 0 | 1 | 0 | 1 | 2 | 124 | 1 | 0 | | production 2 | 0 | 0 | 1 | 0 | 5 | 3 | 109 | 1 | 0 | 0 | 0 | 1 | 0 | 5 | 3 | 109 | 1 | 0 | | production 4 | 0 | 0 | 0 | 0 | 0 | 2 | 40 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 40 | 0 | 0 | | production facilities | 0 | 0 | 2 | 0 | 4 | 13 | 597 | 5 | 0 | 0 | 0 | 2 | 0 | 4 | 13 | 597 | 5 | 0 | | programming | 0 | 0 | 0 | 0 | 1 | 0 | 28 | 3 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 28 | 3 | 0 | | promotion | 0 | 0 | 0 | 0 | 1 | 0 | 25 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 25 | 1 | 0 | | sales 1 | 0 | 0 | 0 | 0 | 0 | 0 | 19 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 19 | 0 | 0 | | sales 2 | 0 | 0 | 1 | 0 | 0 | 0 | 9 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 9 | 2 | 0 | +------------------------------------+----+-----+-----+----+----+----+-----+----+----+----+-----+-----+----+----+----+-----+----+----+
if know years have, following query data in form want. change field list if want them in order or columns omitted in example.
select * rekap_teams.tblrekappendidikan_divisitahunan t2014 join rekap_teams.tblrekappendidikan_divisitahunan t2015 on(divisi) t2014.year = 2014 , t2015.year=2015 replace * order of column names.
Comments
Post a Comment