Aggregating over multiple columns

  • Hello friends,

    See setup code below.

    I have a working solution but I am concerned that the DISTINCT is going to kill performance.

    I am hoping to see some alternative ways to solve this problem so I can compare performance with my real data.

    DROP TABLE IF EXISTS #Sales

    CREATE TABLE #Sales
    (
    Country VARCHAR (3),
    City VARCHAR (20),
    PercentOfTotal DECIMAL (5,2)
    )

    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('CAN','Toronto', 5.50)
    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('CAN','Vancouver', 34.50)
    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('CAN','Montreal', 3.00)
    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('CAN','Montreal', 5.00)

    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('USA','Detroit', 10.50)
    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('USA','Houston', 2.50)
    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('USA','Houston', 15.50)
    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('USA','Houston', 1.50)
    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('USA','Denver', 7.5)
    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('USA','Atlanta', 5.00)

    INSERT INTO #Sales (Country,City,PercentOfTotal) VALUES ('MEX','Tijuana', 9.50)

    --By definition PercentOfTotal will equal 0
    --SELECT SUM(PercentOfTotal) FROM #Sales

    --Aggregation with one column
    SELECT Country,SUM(PercentOfTotal) FROM #Sales GROUP BY Country
    SELECT City,SUM(PercentOfTotal) FROM #Sales GROUP BY City

    --I want to see each City as percent of it's country not the total
    SELECT
    DISTINCT
    Country,City,
    SUM(PercentOfTotal) OVER(PARTITION BY Country) AS PercentCountryOverTotal,
    SUM(PercentOfTotal) OVER(PARTITION BY City) AS PercentCityOverTotal,
    (SUM(PercentOfTotal) OVER(PARTITION BY City)/SUM(PercentOfTotal) OVER(PARTITION BY Country) ) * 100 AS PercentOfCityOverCountry
    FROM #Sales
    ORDER BY Country
  • Here is another version of your query which looks a bit tidier (as it does not rely on DISTINCT to remove dupes). But I'm not sure it will perform any better.

    WITH InitialResults
    AS (SELECT s1.Country
    ,s1.City
    ,PercentOfTotal = SUM (s1.PercentOfTotal)
    FROM #Sales s1
    GROUP BY s1.Country
    ,s1.City)
    SELECT i.Country
    ,i.City
    ,PercentCountryOverTotal = SUM (i.PercentOfTotal) OVER (PARTITION BY i.Country)
    ,PercentCityOverTotal = i.PercentOfTotal
    ,PercentOfCityOverCountry = (SUM (i.PercentOfTotal) OVER (PARTITION BY i.City)
    / SUM (i.PercentOfTotal) OVER (PARTITION BY i.Country)
    ) * 100
    FROM InitialResults i
    ORDER BY i.Country;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply