SQL JOIN and SUM function causes huge number differences

Question

I've trying to get the sum of each customer on every month. However, whenever I join the tables with the calendar table, I get a very high number. The order of the sales are the same.. so I think the sum function is being used several times..

Does anyone know how to fix this..?

[This code causes a smaller number]

SELECT 
s.payer_account_id
, s.month_id
, SUM(s.sales_revenue) AS total_sales
FROM dev.assessment.fact_sales_revenue s
GROUP BY 
s.payer_account_id
, s.month_id
ORDER BY total_sales;

Results are :

Payeraccountid month_id total_sales

1 201903 -248182

2 201907 -196241

3 202008 -172717

4 201908 -160415

[The results ascending are the same but this code causes a huge number]

SELECT 
    s.payer_account_id
    , s.month_id
    , SUM(s.sales_revenue) AS total_sales
    , c.month_code
    , c.year_id
    , c.prior_year_id
    FROM dev.assessment.fact_sales_revenue s
INNER JOIN dev.assessment.dim_calendar c
ON s.month_id = c.month_id
GROUP BY 
    s.payer_account_id
    , s.month_id
    , c.month_code
    , c.year_id
    , c.prior_year_id
ORDER BY total_sales;

Results are : Payeraccountid |month_id | total_sales | month_code
1 | 201903 | -7693657 | MAR

2 | 201907 | -5887230 | JUL

3 | 202008 | -5181517 | AUG

4 | 201908 | -4972869 | AUG

Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, May 18 '22 at 23:01

Asgar · Answer 1 · 2022-05-18T16:27:41.103

Yes you are right about it. Your data is multiplied by almost 30 times in each row. Why don't you join your primary results with dev.assessment.dim_calendar c. Something like:

SELECT a.*, c.<your_necessary_columns> FROM (
SELECT 
    s.payer_account_id
    , s.month_id
    , SUM(s.sales_revenue) AS total_sales
    FROM dev.assessment.fact_sales_revenue s
    YOUR CLAUSES) a 
    INNER JOIN dev.assessment.dim_calendar c
    ON a.month_id = c.month_id
    YOUR CLAUSES

I had previously asked for some minimum reproducible data. Without it I can just give you an execution plan not the exact query. Good luck! Let me know if it works.

SQL JOIN and SUM function causes huge number differences

1 Answers1