[SOLVED] Need Math Help

[Heavy_Kevy]

OK what im trying to get done is the percent of questions that were answered.  Now the number of questions are in one table and the amount of questions answered are in a separate table.  So I add up all the questions and divide it by 100 in one record set.  Then I add up all the answers in its own record set.  But what i cant seem to do is multiply them together.  Now i tried to simplify this by putting them both into one record set like this but it kept returning 0.

 

SELECT ((COUNT(rm.MpText) / 100) * COUNT(td.Data)) * 100

FROM 'tours' AS t

LEFT JOIN route_mps AS rm ON rm.RouteID = t.RouteID

LEFT JOIN tour_data AS td ON td.TourID = t.RouteID

WHERE t.RouteID = $RouteID AND td.Data > 0

GROUP BY RouteID

 

Any help would be greatly appreciated.

[beebum]

Try something like this:

 

SELECT ((COUNT(rm.MpText) / 100) * COUNT(td.Data)) * 100

FROM 'tours' AS t

LEFT JOIN route_mps AS rm ON rm.RouteID = t.tours    # You did "...FROM 'tours'..." so the first join should be tours.

LEFT JOIN tour_data AS td ON td.TourID = t.RouteID

WHERE t.RouteID = $RouteID AND td.Data > 0

GROUP BY RouteID

[Heavy_Kevy]

Thanks for the reply but that didnt work.  But I changed this line to

 

LEFT JOIN tour_data AS td ON td.TourID = t.TourID

 

And im getting an answer its just not the one i want. I want to see 40 but right now its coming up with 16.

Now the math looks like this COUNT(rm.MpText)/100 = .100 because there are 10 questions.  COUNT(td.Data) = 4 because only 4 questions were answered out of the 10.  NOw .100 * 4 should be .4000 but for some reason thats not what coming up. any ideas?

[beebum]

That's what I get for thinking I can speed read with accuracy. 

 

I think your select statement should be this:

 

SELECT (COUNT(rm.MpText) * COUNT(td.Data) * 100

 

answered/total * 100 will give you the % answered.

[Heavy_Kevy]

ok maybe im trying to get a little ahead of my skill range.  Let me try to simplify this.  What if i use two separate statements to get the answers i want and then multiplying the two results together in a third statement.  How would i go about doing that?

[beebum]

You said you wanted "the percent of questions that were answered."

 

number answered/total questions * 100 gives you that.

[Heavy_Kevy]

ok thanks alot i got it now