[Solved] SQL query to remove data duplication when join is used

by


ORDER BY clause is used here as per desired output ordering. It’ll meet expectation.

-- SQL Server
SELECT f.Primary_Brand_Key
     , t.Market
     , f.Food
     , t.TV_Spends
     , t.Print_Spends
     , COALESCE(t.TV_Spends, 0) + COALESCE(t.Print_Spends, 0) "Total_Spends"
FROM Food f
INNER JOIN ( SELECT Primary_Brand_Key
                  , Market
                  , SUM(CASE WHEN Medium = 'TV'
                                THEN Spent
                        END) "TV_Spends"
                  , SUM(CASE WHEN Medium = 'Print'
                                THEN Spent
                        END) "Print_Spends"
             FROM Brand
             WHERE Market IS NOT NULL
             GROUP BY Primary_Brand_Key
                    , Market ) t
        ON f.Primary_Brand_Key = t.Primary_Brand_Key
ORDER BY f.food DESC, t.Market DESC;

As per new requirement type column is included in select list and null value in market row is also consider here.

-- Updated Query
SELECT f.Primary_Brand_Key
     , t.Market
     , t.Type
     , f.Food
     , t.TV_Spends
     , t.Print_Spends
     , COALESCE(t.TV_Spends, 0) + COALESCE(t.Print_Spends, 0) "Total_Spends"
FROM Food f
INNER JOIN ( SELECT Primary_Brand_Key
                  , Market
                  , MAX(Type) Type
                  , SUM(CASE WHEN Market IS NOT NULL
                                THEN CASE WHEN Medium = 'TV'
                                        THEN Spent
                                     END
                        END) "TV_Spends"
                  , SUM(CASE WHEN Market IS NOT NULL
                                THEN CASE WHEN Medium = 'Print'
                                        THEN Spent
                                     END
                        END) "Print_Spends"
             FROM Brand
             -- WHERE Market IS NOT NULL
             GROUP BY Primary_Brand_Key
                    , Market ) t
        ON f.Primary_Brand_Key = t.Primary_Brand_Key
ORDER BY f.food DESC, t.Market DESC;

10

solved SQL query to remove data duplication when join is used