CODEWITHSUNDEEP
mysql
Try_Fizzle
Try_Fizzle

Reputation: 49

Getting incorrect value from SQL Math Operation?

I am performing an operation to work out a percentage based on 2 values.

I have a single table that tracks an overall value against months and years.

Table Name - donation_tracker

enter image description here

I am comparing across all months across current and previous year then performing a calculation such as:

Current Value (Month- January, Year- 2014, CityID- 1) / Previous Value (Month- January, Year- 2013, CityID- 1) = Division Value * 100 = New Percentage Value.

Some of the math operations appear to be correct but some are incorrect for example the image below is showing 140 when it should be 130.

enter image description here

The values I am quering are as follows: enter image description here

The column donation_amount is set as

Type = Decimal

Length = 10,2.

Sum should be 130...

SQL CODE-

SELECT city_markers.City_ID, city_markers.City_Name, city_markers.City_Lng, city_markers.City_Lat, SUM( d2.Donation_Amount ) , d1.Month, d1.Year, round( (
D2.Donation_amount / D1.Donation_amount
) *100, 2 )
FROM `city_markers`
INNER JOIN donation_tracker d1 ON city_markers.City_ID = d1.City_ID
INNER JOIN donation_tracker D2 ON d1.month = D2.month
AND d1.month = 'January'
AND d2.month = 'January'
AND d2.year = '2014'
AND D1.year = D2.year -1
AND D1.Location_ID = D2.Location_ID
GROUP BY city_markers.City_ID

Thank you.

Upvotes: 0

Views: 105

Answers (1)

hellcode
hellcode

Reputation: 2698

You do not sum up the amounts here:

round( (D2.Donation_amount / D1.Donation_amount) *100, 2 )

So the result is calculated by the values of each first row:

round( (70 / 50) *100, 2 ) ==> 140

Use sum() to get the intended result:

round( (sum(D2.Donation_amount) / sum(D1.Donation_amount)) *100, 2 )

Upvotes: 1

Related Questions