0

I can't do a SUM properly between two tables and I don't understand the reason. If someone help me, I'll be very greatful.

I have this first query really simple, it works well. The result for 22/01/2020 is Cost="252.263602". This is the correct answer.

SELECT
  Date,
  AdGroupId,
  SUM(A1.Cost)/1000000 AS Cost
FROM
  `table` A1
GROUP BY
  1, 2
ORDER BY
  Date DESC

But I need to join this table with another one to get more information. So to do that, I have this query:

SELECT
  A1.Date,
  A1.AdGroupId,
  SUM(V1.VideoViews) AS VideoViews,
  SUM(A1.Cost)/1000000 AS Cost
FROM
  `table` A1
INNER JOIN
  `table2` V1
ON
 (A1.Date = V1.Date
 AND A1.AdGroupId = V1.AdGroupId)
GROUP BY
  1, 2
ORDER BY
  Date DESC

What is the result for 22/01/2020? Cost = "1009.054408". It's wrong...

I have tried with different JOINS, changing ON with USING... and It doesn't work.

I have tried to join both tables without SUM and it works well. I get info and I can join more columns from both tables.

What am I doing to get a wrong error in SUM?

Thanks so much!

GMB
  • 216,147
  • 25
  • 84
  • 135
nshabi
  • 63
  • 9
  • This involves a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. Write separate aggregations over appropriate rows and/or aggregate a case statement picking rows; join on common unique column sets. Sometimes DISTINCT aggregation can pick the right value after a non-key join. PS Snippets are for css/html/javascript. Use code blocks. PS For code questions give a [mre]. [ask] – philipxy May 19 '20 at 03:54
  • Does this answer your question? [sql sum data from multiple tables](https://stackoverflow.com/questions/2591390/sql-sum-data-from-multiple-tables) – philipxy May 19 '20 at 03:59
  • This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. PS I just googled your title with 'sql site:stackoverflow.com before:2011'. – philipxy May 19 '20 at 04:00

2 Answers2

2

This is a cardinality problem. You have more than you one record per group in both tables, so as a result the same value is sumed more than once. You can exhibit the problem by running the join query without aggregation.

One typical solution is to aggregate in subqueries, then join:

SELECT
    A1.Date,
    A1.AdGroupId
    A1.Cost
    V1.VideoViews
FROM (
    SELECT
        Date,
        AdGroupId,
        SUM(Cost)/1000000 AS Cost
    FROM `table`
    GROUP BY 1, 2
) A1
INNER JOIN (
    SELECT
        Date,
        AdGroupId,
        SUM(VideoViews) AS VideoViews
    FROM `table2`
    GROUP BY 1, 2
) V1
    ON A1.Date = V1.Date
    AND A1.AdGroupId = V1.AdGroupId
GMB
  • 216,147
  • 25
  • 84
  • 135
  • thanks for your help GMB! i just posted the solution. i found the solution after a while of fighting with the code hehe it's a newbie problem ;) – nshabi Feb 02 '20 at 10:36
  • if i would want to aggregate other column with a SUM inside, I would only have to add other JOIN in last part, wouldn't it? thanks! – nshabi Feb 02 '20 at 10:50
1

I solved my problem. I copy the code here just in case someone need it.

thanks.

SELECT
  A1.Date,
  A1.AdGroupId,
  Cost,
  Views
FROM (
    SELECT
      Date,
      AdGroupId,
      SUM(Cost)/1000000 AS Cost
    FROM
      `table1`
      GROUP BY
      Date,
      AdGroupId) A1
  LEFT JOIN (
    SELECT
      Date,
      AdGroupId,
      SUM(VideoViews) AS Views
    FROM
      `table2`
       GROUP BY
      Date,
      AdGroupId) V1
ON
  A1.Date = V1.Date
  AND A1.AdGroupId = V1.AdGroupId
ORDER BY
  Date DESC
nshabi
  • 63
  • 9