CODEWITHSUNDEEP
sqlsql-serverone-to-many
user62829
user62829

Reputation: 11

One to many join with sums in both tables; how to handle duplicates?

I have the following scenario -

Item sales:

    ╔═════╦════════╦════════════╗
    ║ SKU ║ ItemId ║ SaleAmount ║
    ╠═════╬════════╬════════════╣
    ║ 123 ║      1 ║ $45.99     ║
    ║ 456 ║      2 ║ $54.99     ║
    ╚═════╩════════╩════════════╝

Sale coupons:

    ╔═════╦══════════╦══════════════╗
    ║ SKU ║ CouponId ║ CouponAmount ║
    ╠═════╬══════════╬══════════════╣
    ║ 123 ║        1 ║ $4           ║
    ║ 123 ║        2 ║ $5           ║
    ║ 123 ║        3 ║ $2           ║
    ║ 456 ║        1 ║ $2           ║
    ╚═════╩══════════╩══════════════╝

Desired end result

    ╔═════╦════════════╦══════════════╗
    ║ SKU ║ SaleAmount ║ CouponAmount ║
    ╠═════╬════════════╬══════════════╣
    ║ 123 ║ $45.99     ║ $11          ║
    ║ 456 ║ $54.99     ║ $2           ║
    ╚═════╩════════════╩══════════════╝

I already have this working by joining a filtered item sales query to a filtered sale coupons query and grouping by sku, however I want to know if there is a better way to do this that doesn't result in the SaleAmount being duplicated for each record in sale coupons. i.e. if you were to just do a LEFT JOIN on sale coupons and SUM(SaleAmount), SUM(CouponAmount) and GROUP BY SKU, you'd get -

    ╔═════╦════════════╦══════════════╗
    ║ SKU ║ SaleAmount ║ CouponAmount ║
    ╠═════╬════════════╬══════════════╣
    ║ 123 ║ $137.97    ║ $11          ║
    ║ 456 ║ $54.99     ║ $2           ║
    ╚═════╩════════════╩══════════════╝

Which is correct for sku=456, but falls apart when there is more than one corresponding sale coupon record.

Upvotes: 0

Views: 58

Answers (3)

Yahya Mukhtar
Yahya Mukhtar

Reputation: 474

This will help you. You need to group by with SKU and SaleAmount

    select i.sku,SaleAmount,sum(CouponAmount) as CouponAmount from item i join sale s 
    on i.sku=s.sku group by i.sku,i.SaleAmount
--Edited

Upvotes: 0

Jesús López
Jesús López

Reputation: 9241

Try this:

WITH S AS 
(
   SELECT SKU, SUM(SaleAmount) AS SaleAmount
   FROM Sales
   GROUP BY SKU
), 
C AS 
(
   SELECT SKU, SUM(CouponAmount) AS CouponAmount
   FROM Coupons
   GROUP BY SKU
)
SELECT ISNULL(S.SKU, C.SKU) AS SKU, S.SaleAmount, C.CouponAmount
FROM S FULL OUTER JOIN C ON S.SKU = C.SKU

Upvotes: 2

Harry
Harry

Reputation: 2941

you can do something like this :

;with item_sales as (
select 
123 as sku
,1 as itemid
,45.99 as salesamount

union all

select 
456 as sku
,2 as itemid
, 54.99 as salesamount
)
Select item_sales.* into #itemsales from item_sales


;with mycte as (
select 
123 as sku
,1 as couponid
,4 as couponamount

union all

select 
123 as sku
,2 as couponid
,5 as couponamount
union all

select 
123 as sku
,3 as couponid
,2 as couponamount
union all

select 
456 as sku
,1 as couponid
,2 as couponamount
)

Select
 mycte.sku
 ,ist.salesamount
 ,sum(couponamount )  as couponamount 
from mycte

inner join #itemsales  ist
    on  ist.sku = mycte.sku
group by 
 mycte.sku
 ,ist.salesamount

 drop table #itemsales

Upvotes: 0

Related Questions