Click here to Skip to main content
15,393,706 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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|<br />
|------------|-------------|----------|--------------|<br />
| 2022-01-01 |     1       |   110    |    50        |<br />
| 2022-01-25 |     2       |   1000   |    40        |<br />
| 2022-01-20 |     3       |   2000   |    10        |<br />
| 2022-01-01 |     1       |   110    |    50        |<br />
| 2022-01-25 |     2       |   1000   |    40        |<br />
| 2022-01-20 |     3       |   2000   |    10        |


Expected Output:
<br />
    | product_sku |page_views|number_of_subs|<br />
    |-------------|----------|--------------|<br />
    |     1       |   220    |    100       |<br />
    |     2       |   2000   |    80        |<br />
    |     3       |   4000   |    20        |


What I have tried:

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

SQL
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;
Posted
Updated 24-Jun-22 18:51pm
Comments
Member 15627495 25-Jun-22 0:58am
   
use "select distinct ( pr.productsku) ......"

the DISTINCT keywords make the same values in the column display as 'one'

1 solution

GROUP BY doesn't work like that: the more criteria columns you add to a GROUP BY clause, the more rows you get, not the fewer.

And you are probably doing that because your first tries gave you an error message - have a look here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] and you should strta to see why, and what you need to do.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900