CODEWITHSUNDEEP
mysqlaggregate-functions
user2298034
user2298034

Reputation: 101

MySql calculation in aggregate

I have created this working statement for calculating "C" by using the formula C=A/B*1000000.

This works as expected and the C column is calculated correctly:

Select chemicals.Region As Region,
chemicals.Totalt As `A`,
`area`.`m2` As `B`,
((chemicals.Totalt / `area`.`m2`) * 1000000) As C
From (chemicals Join `area` On chemicals.branch = `area`.branch)

Now I need to use the same formula in an aggregated report, so I tried this:

Select chemicals.Region As Region,
sum(chemicals.Totalt) As `A`,
sum(`area`.`m2`) As `B`,
sum( ((chemicals.Totalt / `area`.`m2`) * 1000000)) As C
From (chemicals Join `area` On chemicals.branch = `area`.branch) GROUP BY Region

But then the value of "C" is not calculated correctly.

I am sure there is a way to get this right, but simply adding a sum sum function to the "C" calculation was not correct. (The columns A and B are correct, by the way).

Thanks for all help!

Upvotes: 0

Views: 244

Answers (1)

Drudge
Drudge

Reputation: 528

As figured out in the comments, the following solution will work:

((sum(chemicals.TotalT) / sum(area.m2)) * 1000000) as C

Upvotes: 1

Related Questions