How to sum column C and D if columns A = Starmer and B = Elected.

Dear HelloWorldMan

Thank you for posting your question on the Q&A forum.

Based on the table and the description you provided, please try the following SQL queries to see if they meet your requirements. The first query calculates totals for each single‑winner scenario. The second query builds on the first to calculate results for every possible two‑winner pair.

After running them, please review the Result columns to ensure they align with your expected logic and payout rules.

Query 1: In your database, go to Create > SQL View, paste the code, and save it as qry_Totals

SELECT
  Q1.Outcome,
  Q1.TotalElected,
  Q1.TotalNotElected,
  Q1.TotalElected AS WinnerElectedResult,
  (
    SELECT
      Sum(T2.NotElectedPosition)
    FROM
      tblBets AS T2
  ) - Q1.TotalNotElected AS LoserNotElectedResult
FROM
  (
    SELECT
      Outcome,
      Sum(ElectedPosition) AS TotalElected,
      Sum(NotElectedPosition) AS TotalNotElected
    FROM
      tblBets
    GROUP BY
      Outcome
  ) AS Q1;

And the output will look like this screenshot:

Query 2: Pairs Combination

This query calculates the combined results for every pair of winners (for example, “Starmer & Farage”). It depends on qry_Totals, so please ensure Query 1 is created and runs successfully first. Then create a new SQL query and paste the following:

SELECT
  T1.Outcome AS Winner1,
  T2.Outcome AS Winner2,
  T1.TotalElected + T2.TotalElected AS PairWinnerElectedResult,
  (
    DSum("NotElectedPosition", "tblBets")
  ) - (
    T1.TotalNotElected + T2.TotalNotElected
  ) AS PairLoserNotElectedResult
FROM
  qry_OutcomeTotals AS T1,
  qry_OutcomeTotals AS T2
WHERE
  T1.Outcome < T2.Outcome
ORDER BY
  T1.Outcome,
  T2.Outcome;

The table will look like the screenshot below

Please run both queries and verify that the numbers in the Result columns match your payout logic. If these queries don’t fully address your needs, feel free to share more details about your expected output or any constraints so I can assist you further.

Looking forward to your update.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.