I have a SQL server with the following columns: brand, collection, gross_profit. There are two brands, each having several collections. I am trying to sort the collections by brand as a percentage of profit they make up for each brand. A sample desired output would look like this:

I have tried the following, which is obviously wrong because SQL does not recognize columns that I have previously created within the same query.

SELECT 
    brand,
    collection,
    gross_profit,
    SUM(gross_profit) OVER (PARTITION BY brand, collection) AS collection_gross_profit_total,
    SUM(gross_profit) OVER (PARTITION BY brand) AS brand_gross_profit_total,
    ((collection_gross_profit_total / brand_gross_profit_total) * 100) AS gross_profit_percentage
FROM dbo.Data$    
WHERE Year='2018'
GROUP BY brand, collection, gross_profit;

Error Messages:

Msg 207, Level 16, State 1, Line 94 Invalid column name 'collection_gross_profit_total'.

Msg 207, Level 16, State 1, Line 94 Invalid column name 'brand_gross_profit_total'.

CodePudding user response：

Another option

select t.brand,
       t.collection,
       t.gross_profit,
       t.collection_gross_profit_total, t.brand_gross_profit_total,
       ((t.collection_gross_profit_total / convert(decimal(16,2), t.brand_gross_profit_total)) * 100) AS gross_profit_percentage
from   ( SELECT brand,
                collection,
                gross_profit,
                SUM(gross_profit) OVER (PARTITION BY brand, collection) AS collection_gross_profit_total,
                SUM(gross_profit) OVER (PARTITION BY brand) AS brand_gross_profit_total
         FROM dbo.Data    
         --WHERE Year='2018'
         GROUP BY brand, collection, gross_profit
       ) t

The DBFiddle contains also another option to do it

CodePudding user response：

If you need to us a calculation more than once you can copy it each time that you want to use it. This is often clearer than a CTE or sub-query but each query on it's own merits.
In this case we don't want to use it more than once so it's just a question of writing the calculations in the place where they're needed.

create table Data (
brand varchar(10),
collection varchar(10),
gross_profit int,
year int);
insert into Data (brand, 
collection, gross_profit) values
('One','OneA',340),
('One','OneB',330),
('One','OneC',330),
('Two','TwoA',500),
('Two','TwoB',400),
('Two','TwoC',100);

SELECT 
    brand,
    collection,
    gross_profit,
    100 * gross_profit / 
    SUM(gross_profit) OVER (PARTITION BY year,brand) AS brand_gross_profit_total,
    100 * gross_profit / 
    SUM(gross_profit) OVER (PARTITION BY year) AS gross_profit_total

FROM Data 
/* WHERE Year='2018' */
GROUP BY year,brand, collection, gross_profit;
GO

brand | collection | gross_profit | brand_gross_profit_total | gross_profit_total
:---- | :--------- | -----------: | -----------------------: | -----------------:
One   | OneA       |          340 |                       34 |                 17
One   | OneB       |          330 |                       33 |                 16
One   | OneC       |          330 |                       33 |                 16
Two   | TwoA       |          500 |                       50 |                 25
Two   | TwoB       |          400 |                       40 |                 20
Two   | TwoC       |          100 |                       10 |                  5

db<>fiddle here