dimasadhipradana dimasadhipradana - 2 months ago 12
MySQL Question

how to fix query mysql with multiple sum

I have a query data from sum function:

ROUND(((nominal)*12) * ROUND((SUM((a.NCI)/3*(60/100))+SUM((b.NSI)/3*(40/100)))/3,2)/100,2) AS nominal_persentasi,
ROUND((((nominal)*12) * ROUND((SUM((a.NCI)/3*(60/100))+SUM((b.NSI)/3*(40/100)))/3,2))*(1.1/100)/100,2) AS tambah_persentasi,
ROUND((((nominal)*12) * ROUND((SUM((a.NCI)/3*(60/100))+SUM((b.NSI)/3*(40/100)))/3,2))+((((nominal)*12) * ROUND((SUM((a.NCI)/3*(60/100))+SUM((b.NSI)/3*(40/100)))/3,2))*(1.1/100))/100,2) AS total_penyesuaian


And the results are:

nominal_persentasi | tambah_persentasi | total_penyesuaian
12.000 3.000 1.203.000


The results produced should be 15,000 , why did it happen ?

I tried to sum the variable
nominal_persentasi + tambah_persentasi
but the result is
0
.

Answer

You are missing a division by 100 in your total. Hence, instead of adding 12,000 and 3,000 to get 15,000 you were actually adding 12,000,000 and 3,000 to get 12,003,000.

SELECT ROUND(( (nominal)*12) *  ROUND((SUM((a.NCI)/3*(60/100))+SUM((b.NSI)/3*(40/100)))/3,2)/100,2) AS nominal_persentasi,
       ROUND((((nominal)*12) *  ROUND((SUM((a.NCI)/3*(60/100))+SUM((b.NSI)/3*(40/100)))/3,2))*(1.1/100)/100,2) AS tambah_persentasi,
       ROUND((((nominal)*12) *  ROUND((SUM((a.NCI)/3*(60/100))+SUM((b.NSI)/3*(40/100)))/3,2)/100) + ((((nominal)*12) *  ROUND((SUM((a.NCI)/3*(60/100))+SUM((b.NSI)/3*(40/100)))/3,2))*(1.1/100))/100, 2) AS total_penyesuaian
FROM yourTable                      -- your query was missing this division by 100           ^^^
Comments