title: MySql calculate multiple column
body: <p>I have code below and in my query but the results it gives me is way off chart, so I thought maybe my calculation is wrong. <code>The Logic</code> is to have multiple column <code>sum</code> <code>minus</code> and <code>times</code> to eachother and the result based on my database numbers has to be <code>4.581.709.50</code> but I get something like <code>164.610.000</code></p>
  <pre><code> SUM(c.gaji_pokok + c.uang_makan + c.tunjangan + c.kendaraan + c.overtime + c.komisi + c.lain_lain + c.cuti - 
                  m.pot_absen_hari * m.pot_absen_rate - IFNULL(g.pot_absen_hari * g.pot_absen_rate, 0) - CONCAT((c.uang_makan)/0.25)*0.05 -
                  n.pot_komisi_dl - n.pot_komisi_p312 - n.pot_komisi_mteg - IFNULL(g.pot_komisi_kasbon, 0) - q.bpjs4 - o.pot_ppn_21pt - o.pot_pinjaman - o.pot_ppn21 - o.pot_bayar_bonus - 
                  o.pot_bayar_thr - c.cuti) as bulan_ppn21,
  </code></pre>
  <h1>Query</h1>
  <pre><code>SELECT
          a.nip,
  
              SUM(c.gaji_pokok + c.uang_makan + c.tunjangan + c.kendaraan + c.overtime + c.komisi + c.lain_lain + c.cuti - 
              m.pot_absen_hari * m.pot_absen_rate - IFNULL(g.pot_absen_hari * g.pot_absen_rate, 0) - CONCAT((c.uang_makan)/0.25)*0.05 -
              n.pot_komisi_dl - n.pot_komisi_p312 - n.pot_komisi_mteg - IFNULL(g.pot_komisi_kasbon, 0) - q.bpjs4 - o.pot_ppn_21pt - o.pot_pinjaman - o.pot_ppn21 - o.pot_bayar_bonus - 
              o.pot_bayar_thr - c.cuti) as bulan_ppn21,
              
              IFNULL((
              CASE
                  WHEN ((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)&lt;=50000000)
              THEN  (0.05*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)))
                  WHEN ((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)&lt;=250000000)
              THEN  (0.15*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.15)-(q.jht*12)))
                  WHEN ((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)&lt;=500000000)
              THEN  (0.25*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.25)-(q.jht*12))) end),0) as tahun_pph21,
          IFNULL((
              CASE
                  WHEN ((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)&lt;=50000000)
              THEN  (0.05*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)))
                  WHEN ((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)&lt;=250000000)
              THEN  (0.15*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.15)-(q.jht*12)))
                  WHEN ((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)&lt;=500000000)
              THEN  (0.25*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.25)-(q.jht*12))) end) -(
              CASE
                  WHEN ((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)&lt;=50000000)
              THEN  (0.05*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)))
                  WHEN ((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)&lt;=250000000)
              THEN  (0.15*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.15)-(q.jht*12)))
                  WHEN ((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)&lt;=500000000)
              THEN  (0.25*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.25)-(q.jht*12))) end)/12,0) - (
              CASE
                  WHEN (((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate))&lt;=50000000)
              THEN  (0.05*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate)))
                  WHEN (((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate))&lt;=250000000)
              THEN  (0.15*((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate))-5000000)
                  WHEN (((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate))&lt;=500000000)
              THEN  (0.25*(0.03*(c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate))-55000000)*1.2
                  WHEN (((c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate))&lt;=500000000)
              THEN  (0.25*(0.03*(c.gaji_pokok * 12)-((c.gaji_pokok * 12)*0.05)-(q.jht*12)-(r.pt_kp_rate))-55000000)*1.2 end) as tot_pph21
  FROM `t_pegawai` a
          LEFT JOIN t_penggajian_karyawan c ON c.nip=a.nip
          LEFT JOIN t_departemen d ON d.id_departemen=a.id_departemen
          LEFT JOIN t_jabatan e ON e.id_jabatan=a.id_jabatan
          LEFT JOIN t_perusahaan f ON f.kode_unitbisnis = a.unit_bisnis
          LEFT JOIN absensi k ON k.pin = a.pin
          LEFT JOIN t_periode l ON l.nama_periode=c.bulan  and YEAR(l.periode_start) = c.tahun
          LEFT JOIN t_potongan_absen m ON m.nip=a.nip and m.nip=c.nip and  m.bulan = l.id_periode and m.tahun = YEAR(l.periode_start)
          LEFT JOIN t_potongan_gaji g ON g.nip=a.nip and g.nip=c.nip and  g.bulan = l.id_periode and g.tahun = YEAR(l.periode_start)
          LEFT JOIN t_potongan_komisi n ON n.nip=a.nip and n.nip=c.nip and n.bulan = l.id_periode and n.tahun = YEAR(l.periode_start)
          LEFT JOIN t_potongan_ppn o ON o.nip=a.nip and o.nip=c.nip and  o.bulan = l.id_periode and o.tahun = YEAR(l.periode_start)
          LEFT JOIN t_jenjang_bpjs q ON q.nip=a.nip  and q.tahun = YEAR(l.periode_start)
          LEFT JOIN t_ptkp r ON r.pt_kp_name=a.status_ptkp
  WHERE l.id_periode='8' AND f.kode_unitbisnis ='PJS-001' and k.Tanggal &gt;= l.periode_start and k.Tanggal &lt;= l.periode_end
  GROUP BY a.pin
  </code></pre>
  <p><strong>Any suggestion on calculation way to make it right?</strong></p>