Heavy_Kevy Posted January 22, 2008 Share Posted January 22, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/87237-solved-need-math-help/ Share on other sites More sharing options...
beebum Posted January 22, 2008 Share Posted January 22, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/87237-solved-need-math-help/#findComment-446297 Share on other sites More sharing options...
Heavy_Kevy Posted January 22, 2008 Author Share Posted January 22, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/87237-solved-need-math-help/#findComment-446364 Share on other sites More sharing options...
beebum Posted January 22, 2008 Share Posted January 22, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/87237-solved-need-math-help/#findComment-446388 Share on other sites More sharing options...
Heavy_Kevy Posted January 23, 2008 Author Share Posted January 23, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/87237-solved-need-math-help/#findComment-447043 Share on other sites More sharing options...
beebum Posted January 23, 2008 Share Posted January 23, 2008 You said you wanted "the percent of questions that were answered." number answered/total questions * 100 gives you that. Quote Link to comment https://forums.phpfreaks.com/topic/87237-solved-need-math-help/#findComment-447094 Share on other sites More sharing options...
Heavy_Kevy Posted January 23, 2008 Author Share Posted January 23, 2008 ok thanks alot i got it now Quote Link to comment https://forums.phpfreaks.com/topic/87237-solved-need-math-help/#findComment-447139 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.