?
Solved

Remove duplicates from calculation

Posted on 2012-09-17
3
Medium Priority
?
337 Views
Last Modified: 2012-09-17
This is a mysql 5.5.x server to start off with.

I have an issue that has caused duplication in the table and I'm trying to get a quick fix until we can figure out why the front it is allowing duplication.

Problem: So what its doing is adding duplicate questionid's which is throwing off my calculations. Attached is an snippet of the table, look at questionid 27866, you see it has a duplicate. I thought I could just add distinct, but no luck. Below is the  query. Looking for a solution that will not put duplicate questionids into the calculation.

select tg_results.questionid, sum(tg_questions.points*tg_results.correct) AS ptotal, sum((tg_questions.points*tg_results.correct)/#total.total#) AS per
from tg_results, tg_questions
where tg_questions.questionid = tg_results.questionid AND tg_results.studentid = 10001 AND tg_results.testid = 1861

Open in new window


Thought I could just add distinct, but that doesn't seem to work.

select distinct tg_results.questionid, sum(tg_questions.points*tg_results.correct) AS ptotal, sum((tg_questions.points*tg_results.correct)/#total.total#) AS per
from tg_results, tg_questions
where tg_questions.questionid = tg_results.questionid AND tg_results.studentid = 10001 AND tg_results.testid = 1861

Open in new window


Snippet of the table structure and error
0
Comment
Question by:-Matthew-
3 Comments
 
LVL 5

Expert Comment

by:sameer_goyal
ID: 38405003
Try this

select tg_results.questionid, sum(tg_questions.points*tg_results.correct) AS ptotal, sum((tg_questions.points*tg_results.correct)/#total.total#) AS per
from tg_results inner join tg_questions ON tg_questions.questionid = tg_results.questionid
where tg_results.studentid = 10001 AND tg_results.testid = 1861


I basically am introducing an inner join in the query
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38405223
Within SQL ... the DISTINCT is applied to the output, not the input.  So by the time the DISTINCT is applied - the duplicates have already been processed.
To be able to use the DISTINCT ... you would need to move it earlier in the execution - like an inline view. e.g.
select tg_results.questionid
     , sum(tg_questions.points*tg_results.correct) AS ptotal
     , sum((tg_questions.points*tg_results.correct)/#total.total#) AS per
from (select DISTINCT questionid, points
      from tg_results
      where tg_results.studentid = 10001 AND tg_results.testid = 1861) tg_results
   , tg_questions
where tg_questions.questionid = tg_results.questionid

Open in new window

0
 

Author Closing Comment

by:-Matthew-
ID: 38405478
Thanks for the explanation, its not just getting an answer, but understanding it.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question