query structure - ignoring entries for the same event from multiple users?

Question

One table in my MySQL database tracks game plays. It has the following structure:

SCENARIO_VICTORIES
[ID]  [scenario_id]  [game]  [timestamp]  [user_id]  [winning_side]  [play_date]

ID is the autoincremented primary key. timestamp records the moment of submission for the record. winning_side has one of three possible values: 1, 2, or 0 (meaning a draw)

One of the queries done on this table calculates the victory percentage for each scenario, when that scenario's page is viewed. The output is expressed as:

• Side 1 win %
• Side 2 win %
• Draw %

and queried with:

SELECT winning_side, COUNT(scenario_id)
FROM scenario_victories WHERE scenario_id='$scenID'
GROUP BY winning_side ORDER BY winning_side ASC

and then processed into the percentages and such.

Sorry for the long setup. My problem is this: several of my users play each other, and record their mutual results. So these battles are being doubly represented in the victory percentages and result counts. Though this happens infrequently, the userbase isn't large and the double entries do have a noticeable effect on the data.

Given the table and query above - does anyone have any suggestions for how I can "collapse" records that have the same play_date & game & scenario_id & winning_side so that they're only counted once?

Answer 1

It's a bit late but ideally you'd filter these duplicates out when they're inserted.

That said, you should be able to do a DISTINCT inner query like so:

SELECT winning_side, COUNT(scenario_id)
FROM (
    SELECT DISTINCT play_date, game, scenario_id, winning_side
    FROM scenario_victories 
    WHERE scenario_id = '$scenID'
) 
GROUP BY winning_side ORDER BY winning_side ASC