Mysql average percent

[wipeskim]

Hi,

I am trying to get an average percentage for child discounts on hotels. I'll describe what i go so far. I have a table with:

ID | Contract_id | from_age | to_age | discount |
1 | 15 | 0 | 6 | 100 |
2 | 15 | 7 | 12 | 50 |

My example that i am running is for 3 children on a booking:

1st child : 3 years old
2nd child: 4 years old
3rd child: 9 years old

So according to the table the 1st child has 100% discount so as the second, the third child with 9 years old has 50% , summing all discounts per each child should be a total of 250% , now to calculate the average i divide by the number of children wich the average percent is now: 83.33% .

Now my problem is to put this on mysql query without using server side code such as php. this is what i got so far.

SELECT sum(discount) as average FROM reductions WHERE from_age<=9 and to_age>=2

When i perform this it gives the total percentage of 150% instead of 250%

can anyone help me with this?

thanks

[bretticus]

150% is right. You only selected 2 rows. One has a value of 100 and one has 50 (100 + 50 = 150.) Now I suspect you already know this, but wanted to point it out just in case.

What you are probably going to have to do is run separate queries. The reason being is, you need some way to specify that you have 2x100 percent. Thus, unless you have this represented in your database somehow, you'll have to inject it into your query individually:

SELECT discount as average FROM reductions WHERE 3 BETWEEN from_age AND to_age

Based on the information you gave, there might be a way to pull this 2@discountx, 1@discounty but it's not occurring to me right now. At least I gave you a bump 😃

Good luck!