Only use first of multiple matches SQL

Question

I need to find the sum of credits from click_stats where source = 'reward' for all users who have claimed at least one of the 3 promo codes AND have the total clicks displayed.

Tables:

+---------------------------------------------+
| click_stats                                 |
+----+---------+--------+--------+------------+
| ID | Credits | Userid | Source | Clicked    |
+----+---------+--------+--------+------------+
| 1  | 10      | jon    | reward | 1602216005 |
| 2  | 5       | bob    | reward | 1602216504 |
| 3  | 5       | jon    | reward | 1602216137 |
| 4  | 5       | bob    | reward | 1602216138 |
| 5  | 10      | jon    | reward | 1602216139 |
| 6  | 5       | jon    | reward | 1602216140 |
| 7  | 10      | bob    | reward | 1602216150 |
| 8  | 10      | jon    | reward | 1602216150 |
| 9  | 10      | sue    | reward | 1602216150 |
| 10  | 10     | sue    | prize  | 1602216150 |
+----+---------+--------+--------+------------+

+-----------------------+
| promo_used            |
+----+--------+---------+
| id | userid | promoid |
+----+--------+---------+
| 1  | bob    | 1       |
+----+--------+---------+
| 2  | bob    | 2       |
+----+--------+---------+
| 3  | bob    | 3       |
+----+--------+---------+
| 4  | jon    | 1       |
+----+--------+---------+
| 5  | jon    | 2       |
+----+--------+---------+

+------------------------+
| promo_codes            |
+----+----------+--------+
| id | code     | reward |
+----+----------+--------+
| 1  | forever1 | 20     |
+----+----------+--------+
| 2  | novgfy1  | 250    |
+----+----------+--------+
| 3  | novgfy2  | 500    |
+----+----------+--------+

My Query:

SELECT 
    click_stats.userid  as Name, 
    sum(credits) as TotalClicks 
  FROM click_stats
    JOIN promo_used 
    ON click_stats.userid = promo_used.userid
    JOIN promo_codes 
        ON promo_used.promoid = promo_codes.id
WHERE
   click_stats.source = 'reward' 
   and FROM_UNIXTIME(clicked) > '2020-10-08' 
   and (promo_codes.name like '%forever%' 
        or promo_codes.name like 'novgfy%')
group by 
   Name
having 
   TotalClicks > 10

Results when I run my query (returning the total number of clicks * number of promo codes used):

+--------------------+
| Results            |
+------+-------------+
| Name | TotalClicks |
+------+-------------+
| bob  | 60          |
+------+-------------+
| jon  | 80          |
+------+-------------+

Results I am wanting (the total number of clicks only):

+--------------------+
| Expected Results   |
+------+-------------+
| Name | TotalClicks |
+------+-------------+
| bob  | 20          |
+------+-------------+
| jon  | 40          |
+------+-------------+

The problem (I suspect) is that it is summing the click_stats every time it finds a match for promo_codes.name. I just need to know if one of them has been used and then return the total clicks for that user.

Specify **precise** MySQL version. – Akina Nov 06 '20 at 21:11 — Akina, Nov 06 '20 at 21:11

score 1 · Answer 1 · answered Nov 06 '20 at 21:14

Your understanding of the problem is correct. The join multiplies the rows whenever a user has more than one of the promo codes, and you end up with the same credit summed several times.

I would recommend exists instead:

select cs.userid as name, sum(cs.credits) as totalclicks
from click_stats cs
where cs.source = 'reward' and exists (
    select 1
    from promo_used pu
    inner join promo_codes pc on pc.id = pu.promoid
    where 
        pu.userid = cs.userid
        and (pc.name like '%forever%' or pc.name like 'novgfy%')
)
group by cs.userid
having sum(cs.credits) > 10

Thanks for the suggestion. It has been running for 20+ minutes now so just going to use excel to get what I need I think after having my hosting kill it off for me. I was not able to decipher some of the query you wrote so my fault for trying to guess what you meant. — Jason, Nov 06 '20 at 23:53

Only use first of multiple matches SQL

1 Answers1