php - how to make a query to count, sum, and group by name?

I want to make a report like this, but I'm having trouble making the query.

no	merk	tahun	departemen	lokasi	history 2020	history 2021	Total (2020+2021)
1	machine A	2014	Production	LA 1	1	1	2
2	machine B	2019	Production	LA 2	0	2	2

Answer

Solution:

Try to run this, I hope this can give you idea...

    select merk_tmf merk, tahun_tmf tahun, dept_tmf departemen, lokasi_tmf lokasi,
          sum(case when date_format(waktu_tmf, '%Y') = 2020 then 1 else 0 end) 'history 2020', 
          sum(case when date_format(waktu_tmf, '%Y') = 2021 then 1 else 0 end) 'history 2021', 
          sum((case when date_format(waktu_tmf, '%Y') = 2020 then 1 else 0 end) +
          (case when date_format(waktu_tmf, '%Y') = 2021 then 1 else 0 end)) 'Total (2020+2021)'
    from tb_tmf 
    group by merk_tmf, lokasi_tmf

Source