get sum for on columns year

Question

I try to make a report per year where the year I specify every 4 years. In the report appearing already available year option to be reported. For example options 2013-2016, so in the report it displays data from 2013, 2014, 2015 and 2016. For more details I have the table like below :

+-----------------+--------------+-------------+-------------+ 
| id_jenis_perjal | jenis_perjal |  id_tahun   |   tahun     |
+-----------------+--------------+-------------+-------------+ 
|        7        | RPPJ         |  1          |   2013      |
|        7        | RPPJ         |  1          |   2013      |
|        7        | RPPJ         |  2          |   2014      |
|        7        | RPPJ         |  3          |   2015      |
|        7        | RPPJ         |  4          |   2016      |
|        8        | Rambu        |  1          |   2013      |
|        8        | Rambu        |  2          |   2014      |
|        8        | Rambu        |  2          |   2014      |
|        8        | Rambu        |  3          |   2015      |
|        8        | Rambu        |  4          |   2016      |
|        8        | Rambu        |  4          |   2016      |
|        8        | Rambu        |  4          |   2016      |
+-----------------+--------------+-------------+-------------+

But to display the report I have to modify the table to be like this

+-----------------+------+------+------+------+ 
| jenis_perjal    | 2013 | 2014 | 2015 | 2016 |
+-----------------+------+------+------+------+
|      RPPJ       |   2  |   1  |   1  |   1  |
|      Rambu      |   1  |   2  |   1  |   3  |
+-----------------+------+------+------+------+

Instead of scaisEdge's answer below, consider handling issues of data display in application code (php, in this instance) — Strawberry, May 30 '17 at 05:24
This is called pivot table and has been asked and answered here on SO many times. The duplicate topic describes both static (columns known in advance) and dynamic (columns are determined on the fly) pivoting in mysql. Pls note, it may be more efficient to perform this transformation in the application code, rather than in mysql - as @Strawberry has also pointed out. — Shadow, May 30 '17 at 05:28

score 1 · Answer 1 · answered May 30 '17 at 05:22

You coulduse sum and group based on case when

  select jenis_perjal
      , sum( case when tahum = 2013 then id_tahun end ) as 2013
      , sum( case when tahum = 2014 then id_tahun end ) as 2014
      , sum( case when tahum = 2015 then id_tahun end ) as 2015
      , sum( case when tahum = 2016 then id_tahun end ) as 2016
  from my_table 
  group by jenis_perjal

get sum for on columns year

1 Answers1