'SQL JOIN and SUM function causes huge number differences

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

mysqlsqljoinsumamazon-redshift


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions