Summing all values with same ID in a column give me duplicated values in SQL?-CodePudding

I am trying to sum all the columns that have the same ID number in a specified date range, but it always gives me duplicated values

select pr.product_sku,
       pr.product_name, 
       pr.brand,
       pr.category_name,
       pr.subcategory_name,
       a.stock_on_hand, 
       sum(pr.pageviews) as page_views,
       sum(acquired_subscriptions) as acquired_subs,
       sum(acquired_subscription_value) as asv_value
from dwh.product_reporting pr
join dm_product.product_data_livefeed a 
on pr.product_sku = a.product_sku 
where pr.fact_day between '2022-05-01' and '2022-05-30' and pr.pageviews > '0' and pr.acquired_subscription_value > '0'  and store_id = 1 
group by pr.product_sku,
         pr.product_name,
         pr.brand,
         pr.category_name,
         pr.subcategory_name,
         a.stock_on_hand;

This supposes to give me: Sum of all KPI values for a distinct product SKU
Example table:

|     Date   | product_sku |page_views|number_of_subs
|------------|-------------|----------|--------------|
| 2022-01-01 |     1       |   110    |    50        |
| 2022-01-25 |     2       |   1000   |    40        |
| 2022-01-20 |     3       |   2000   |    10        |
| 2022-01-01 |     1       |   110    |    50        |
| 2022-01-25 |     2       |   1000   |    40        |
| 2022-01-20 |     3       |   2000   |    10        |

Expected Output:

| product_sku |page_views|number_of_subs
|-------------|----------|--------------|
|     1       |   220    |    100       |
|     2       |   2000   |    80        |
|     3       |   4000   |    20        |

Sorry I had to edit to add the table examples

CodePudding user response：

Since you're not listing the dupes (assuming they are truly appearing as duplicate rows, and not just multiple rows with different values), I'll offer that there may be something else that's at play here - I would suggest for every string value in your result set that's part of the GROUP BY clause to apply a TRIM(UPPER()) as you might be dealing with either a case insensitivity or trailing blanks that are treated as unique values in the query.

CodePudding user response：

Based on your query I'd look at it without the join. The most likely culprit is the value you're pulling in from the other table.