how to fix query mysql with multiple sum

Question

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.

i mean my result is not 15.000 but 1.203.000 it look like combine first data and second data not sum the value — user5332276
– user5332276, Commented Sep 6, 2016 at 4:16

Tim Biegeleisen · Accepted Answer · 2016-09-06 04:20:16Z

1

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           ^^^

answered Sep 6, 2016 at 4:20

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user5332276 Over a year ago

okay...so what i must do? should i use decimal or i must fix the query?I have tried to use decimal in that query but the result is 0

Tim Biegeleisen Over a year ago

I just fixed your query, cut and paste what I gave above. Or, better yet, just cut and paste verbatim the expressions for the two columns in the sum. You removed a division by 100 which appeared in the nominal_persentasi column.

1000111 Over a year ago

Nice catch Tim. #EagleEye

Collectives™ on Stack Overflow

how to fix query mysql with multiple sum

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related