Using COUNTIF inside aggregation function

Question

Im making a betting system app. I need to count the points of a user based on his bets, knowing that some of the bets can be 'combined', which makes the calcul a bit more complex than a simple addition.

So if i have 3 bets: {points: 3, combined: false}, {points: 5, combined: true}, {points: 10, combined: true}, there are two bets combined here, so the total points should be 3 + (5 * 2) + (10 * 2). Reality is a bit more complex since the points are not directly in the bet object but in the match it refers to

Here is a part of my query. As you can see, i first check if the bet is right based on the match result, in that case if the bet is combined I multiply it by the value of combinedLength, else i'll just sum the value of that bet. I tried to replicate the COUNTIF inside the CASE, which gaves me an error like 'cannot aggregation inside aggregation'.

SELECT
  JSON_EXTRACT_SCALAR(data, '$.userId') AS userId,
  COUNTIF(JSON_EXTRACT_SCALAR(data, '$.combined') = 'true') AS combinedLength,
  SUM (
    (
      CASE WHEN JSON_EXTRACT_SCALAR(data, '$.value') = match.result
      THEN IF(JSON_EXTRACT_SCALAR(data, '$.combined') = "true", match.odd * combinedLength, match.odd)
      ELSE 0 
      END
    )
  ) AS totalScore,
FROM data.user_bets_raw_latest
INNER JOIN matchLines ON matchLines.match.matchId = JSON_EXTRACT(data, '$.fixtureId')
GROUP BY userId

I've been looking for days... thanks so much for the help !

Please show sample of input data and expected result. See how to present [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — Mikhail Berlyant, Sep 06 '20 at 16:18

score 0 · Answer 1 · answered Sep 06 '20 at 22:18

If I follow you correctly, then you need to count the total number of combined bets per user in a subquery, using a window function. Then, you can use this information while aggregating.

Consider:

select 
    user_id,
    sum(case when combined = 'true' then odd * cnt_combined else odd end) total_score
from (
    select
        u.*,
        m.match.odd,
        countif(b.combined = 'true') over(partition by userid) as cnt_combined,
    from (
        select 
            json_extract_scalar(data, '$.userid')    userid,
            json_extract_scalar(data, '$.combined')  combined,
            json_extract_scalar(data, '$.value')     value,
            json_extract_scalar(data, '$.fixtureid') fixtureid
        from data.user_bets_raw_latest
    ) b
    left join matchlines m 
        on  m.match.matchid = b.fixtureid
        and m.match.result  = b.value
) t
group by userid

I find that it is simpler to use a left join and put the condition on the match result in there.

I moved the json extractions to a subquery to reduce the length of the query.

This looks like an elegant solution :) I ended up doing it a bit differently, by creating an other 'tables' (with WITH) to organize my combined data and to calculate my score. I wasn't aware i could achieve the same thing with OVER and PARTITION BY. It's good to know ! Sorry for the late reply ! — Guillaume K, Sep 15 '20 at 06:52

Using COUNTIF inside aggregation function

1 Answers1