Aggregating over multiple columns

• Chrissy321

  SSCoach

  Points: 15763

  More actions

  May 16, 2024 at 7:24 pm

  #4421789

  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

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  May 17, 2024 at 9:58 am

  #4421900

  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;

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin