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.


    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
    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
    ,PercentOfTotal = SUM (s1.PercentOfTotal)
    FROM #Sales s1
    GROUP BY s1.Country
    SELECT i.Country
    ,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