How can I add a new calculated column using a window function to my SQL query?

Question

I have data that looks like this:

Trader Name      | Currency_Code | Counterparty | Traded_Amount | Total_Traded_Volume | Baseline_Avg | Variance
Jules Winnfield  | GBP           |  GOLD        | 10000         | 30000               | 10000        | 0
Jules Winnfield  | GBP           |  BARC        | 8000          | 30000               | 11000        | -3000
Jules Winnfield  | GBP           |  JPMORG      | 12000         | 30000               | 9000         | +3000
Jules Winnfield  | EUR           |  GOLD        | 15000         | 27000               | 6000         | 21000
Jules Winnfield  | EUR           |  BARC        | 2000          | 27000               | 12500        | -10500
Jules Winnfield  | EUR           |  JPMORG      | 10000         | 27000               | 8500         | +1500

Let me take a minute to briefly explain this dataset:

The trader has placed a collective £30000 worth of trades across three counterparties - say in this example, goldman sachs, barclays and JP Morgan.
The individual amounts, i.e. £10000, £8000 and £12000 are a simple sum() aggregation performed on the individual trades themselves, with the £30000 obtained via another aggregation using OVER (PARTITION BY TRADER_NAME, CURRENCY_CODE)
The baseline_average calculates the average trading volume with all other counterparties - for e.g. Jules has traded £8000 with Barclays, and the average trading volume with the other counterparties (Goldman Sachs and JP Morgan) is £11000. The variance is the difference between traded_amount and baseline_average.

The code used to generate the above output is:

SELECT 

     OT.TRADER_NAME, 
     OT.CURRENCY_CODE, 
     OT.COUNTERPARTY, 
     SUM(OT.TRADED_AMOUNT) AS TRADED_AMOUNT,
     SUM(OT.TRADED_AMOUNT) OVER (PARTITION BY OT.TRADER_NAME, OT.CURRENCY_CODE) AS TOTAL_TRADED_VOL,
     (SUM(OT.TRADED_AMOUNT) OVER (PARTITION BY OT.TRADER_NAME, OT.CURRENCY_CODE)- 
     SUM(OT.TRADED_AMOUNT))/NULLIF(SUM(1) OVER (PARTITION BY OT.TRADER_NAME, OT.CURRENCY_CODE)-1),0) 
     AS BASELINE_AVG,
     SUM(OT.TRADED_AMOUNT) - (SUM(OT.TRADED_AMOUNT) OVER (PARTITION BY OT.TRADER_NAME, 
     OT.CURRENCY_CODE)-SUM(OT.TRADED_AMOUNT))/NULLIF(SUM(1) OVER (PARTITION BY OT.TRADER_NAME, 
     OT.CURRENCY_CODE)-1),0) AS VARIANCE

FROM ORDERS_TRADES_DATA OT
GROUP BY OT.TRADER_NAME, OT.CURRENCY_CODE, OT.COUNTERPARTY, FX.FX_RATE

So far so good. This enables me to slice the data as long as I specify the currency I am interested in. However, I would like to now add a column that aggregates the trader's entire trading volume into the USD equivalent- essentially, one traded_volume per user in USD as a window function - which I can use for analysis. I have the FX rates stored in a separate table and can apply a join. Have tried running the following query:

SELECT 

     OT.TRADER_NAME, 
     OT.CURRENCY_CODE, 
     OT.COUNTERPARTY, 
     SUM(OT.TRADED_AMOUNT) AS TRADED_AMOUNT,
     SUM(OT.TRADED_AMOUNT) OVER (PARTITION BY OT.TRADER_NAME, OT.CURRENCY_CODE) AS TOTAL_TRADED_VOL,
     (SUM(OT.TRADED_AMOUNT) OVER (PARTITION BY OT.TRADER_NAME, OT.CURRENCY_CODE)- 
     SUM(OT.TRADED_AMOUNT))/NULLIF(SUM(1) OVER (PARTITION BY OT.TRADER_NAME, OT.CURRENCY_CODE)-1),0) 
     AS BASELINE_AVG,
     SUM(OT.TRADED_AMOUNT) - (SUM(OT.TRADED_AMOUNT) OVER (PARTITION BY OT.TRADER_NAME, 
     OT.CURRENCY_CODE)-SUM(OT.TRADED_AMOUNT))/NULLIF(SUM(1) OVER (PARTITION BY OT.TRADER_NAME, 
     OT.CURRENCY_CODE)-1),0) AS VARIANCE,
     SUM(OT.TRADED_AMOUNT)/FX.FX_RATE AS TRADED_AMOUNT_USD,
     SUM((SUM(OT.TRADED_AMOUNT)/FX.FX_RATE) AS TOTAL_TRADED_VOL_USD,
     (SUM(OT.TRADED_AMOUNT)/FX.FX_RATE OVER (PARTITION BY OT.TRADER_NAME)- 
     SUM(OT.TRADED_AMOUNT)/FX.FX_RATE)/NULLIF(SUM(1) OVER (PARTITION BY OT.TRADER_NAME)-1),0) 
     AS BASELINE_AVG_USD,
     SUM((SUM(OT.TRADED_AMOUNT)/FX.FX_RATE) - (SUM(OT.TRADED_AMOUNT)/FX.FX_RATE OVER (PARTITION BY 
     OT.TRADER_NAME)-SUM(OT.TRADED_AMOUNT)/FX.FX_RATE)/NULLIF(SUM(1) OVER (PARTITION BY 
     OT.TRADER_NAME)-1),0) AS VARIANCE_USD

FROM ORDERS_TRADES_DATA OT
LEFT JOIN FX_RATES_TABLE FX ON OT.CURRENCY_CODE = FX.ASSET_CURRENCY_CODE
GROUP BY OT.TRADER_NAME, OT.CURRENCY_CODE, OT.COUNTERPARTY, FX.FX_RATE

... does not work as I get the error:

cannot perform an aggregate function on an expression containing an aggregate or subquery.

How an I achieve my objective here?

score 0 · Answer 1 · answered Aug 27 '20 at 21:14

You can write the query this way:

SELECT
     A.TRADER_NAME, 
     A.CURRENCY_CODE, 
     A.COUNTERPARTY, 
     A.TRADED_AMOUNT,
     A.TOTAL_TRADED_VOL,
     A.BASELINE_AVG,
     A.VARIANCE,              
     A.TRADED_AMOUNT/FX.FX_RATE AS TRADED_AMOUNT_USD,
     A.TOTAL_TRADED_VOL/FX.FX_RATE AS TOTAL_TRADED_VOL_USD,
     A.BASELINE_AVG/FX.FX_RATE AS BASELINE_AVG_USD,
     A.VARIANCE/FX.FX_RATE AS VARIANCE_USD
     
FROM   
    (SELECT 
         OT.TRADER_NAME, 
         OT.CURRENCY_CODE, 
         OT.COUNTERPARTY, 
         SUM(OT.TRADED_AMOUNT) AS TRADED_AMOUNT,
         SUM(OT.TRADED_AMOUNT) OVER (PARTITION BY OT.TRADER_NAME, OT.CURRENCY_CODE) AS TOTAL_TRADED_VOL,
         (SUM(OT.TRADED_AMOUNT) OVER (PARTITION BY OT.TRADER_NAME, OT.CURRENCY_CODE)- 
         SUM(OT.TRADED_AMOUNT))/NULLIF(SUM(1) OVER (PARTITION BY OT.TRADER_NAME, OT.CURRENCY_CODE)-1),0) 
         AS BASELINE_AVG,
         SUM(OT.TRADED_AMOUNT) - (SUM(OT.TRADED_AMOUNT) OVER (PARTITION BY OT.TRADER_NAME, 
         OT.CURRENCY_CODE)-SUM(OT.TRADED_AMOUNT))/NULLIF(SUM(1) OVER (PARTITION BY OT.TRADER_NAME, 
         OT.CURRENCY_CODE)-1),0) AS VARIANCE

    FROM ORDERS_TRADES_DATA O) A
LEFT JOIN FX_RATES_TABLE FX ON FX.ASSET_CURRENCY_CODE = A.CURRENCY_CODE

Thanks very much for your proposed solution; I've actually gone with the cte-based solution for now. Much appreciated though. — SQLGIT_GeekInTraining, Aug 29 '20 at 08:31

Parfait · Accepted Answer · 2020-08-28T14:58:44.407

Immediate error is due to layered aggregate SUM calls: SUM((SUM(OT.TRADED_AMOUNT)/FX.FX_RATE). But another error will raise due to missing GROUP BY clause in aggregate query since SELECT includes non-aggregate columns not referenced in GROUP BY.

However, avoid any of the SUM() OVER(...) window functions, and join multiple level of aggregations (at trade/currency level and at trade/currency/counterparty levels). Then run needed calculations in outer query without aggregates. Do note: dividing by zero is undefined.

WITH trader_curr_agg AS (
     SELECT   OT.TRADER_NAME
            , OT.CURRENCY_CODE
            , SUM(OT.TRADED_AMOUNT) AS TOTAL_TRADED_VOL
            , COUNT(*) AS TRADE_COUNTS
     FROM ORDERS_TRADES_DATA OT
     GROUP BY   OT.TRADER_NAME
              , OT.CURRENCY_CODE
),  
    trader_counterparty_agg AS (
     SELECT   OT.TRADER_NAME
            , OT.CURRENCY_CODE
            , OT.COUNTERPARTY
            , SUM(OT.TRADED_AMOUNT) AS TRADED_AMOUNT
     FROM ORDERS_TRADES_DATA OT
     GROUP BY   OT.TRADER_NAME
              , OT.CURRENCY_CODE
              , OT.COUNTERPARTY
)

SELECT
         tcntr.TRADER_NAME
       , tcntr.CURRENCY_CODE
       , tcntr.COUNTERPARTY

       , tcntr.TRADED_AMOUNT
       , tcurr.TOTAL_TRADED_VOL
       , (tcurr.TOTAL_TRADED_VOL - tcntr.TRADED_AMOUNT)
                  / NULLIF(tcurr.TRADE_COUNTS-1, 0) AS BASELINE_AVG
       , (tcntr.TRADED_AMOUNT - (tcurr.TOTAL_TRADED_VOL - tcntr.TRADED_AMOUNT)) 
                  / NULLIF(tcurr.TRADE_COUNTS-1, 0) AS VARIANCE

       , tcntr.TRADED_AMOUNT / FX.FX_RATE AS TRADED_AMOUNT_USD
       , tcurr.TOTAL_TRADED_VOL / FX.FX_RATE AS TOTAL_TRADED_VOL_USD
       , ((tcurr.TOTAL_TRADED_VOL - tcntr.TRADED_AMOUNT) 
                  / NULLIF(tcurr.TRADE_COUNTS-1, 0)) / FX.FX_RATE AS BASELINE_AVG_USD
       , ((tcntr.TRADED_AMOUNT - (tcurr.TOTAL_TRADED_VOL - tcntr.TRADED_AMOUNT)) 
                  / NULLIF(tcurr.TRADE_COUNTS-1, 0)) / FX.FX_RATE AS VARIANCE_USD
 
FROM trader_counterparty_agg tcntr
INNER JOIN trader_currency_agg tcurr
    ON tcntr.TRADER_NAME = tcurr.TRADER_NAME
    AND tcntr.CURRENCY_CODE = tcurr.CURRENCY_CODE
LEFT JOIN FX_RATES_TABLE FX 
    ON tcntr.CURRENCY_CODE = FX.ASSET_CURRENCY_CODE

Thanks very much, I will give this a whirl - apologies i missed writing my full code; I had actually used Group By - the first query worked as I mentioned but the second one (where I'm attempting a USD conversion) didn't... let me try your solution. — SQLGIT_GeekInTraining, Aug 28 '20 at 05:53
Understood. Reading closer your error is actually due to layering `SUM` in one of your calculations: `SUM((SUM(OT.TRADED_AMOUNT)/FX.FX_RATE)`. But missing `GROUP BY` is also an issue. Yes, please consider this solution. You avoid the many `SUM() OVER()` calls for readability and even calculate sums once for efficiency. Please adjust formulas as needed if my untested translations yield issues. Real difference is `_USD` columns are divided by `FX_RATE` — Parfait, Aug 28 '20 at 15:02
Apologies for the delayed response, spent quite a bit of time on this yest - I was able to successfully adapt your solution, spent some time adding my own tweaks to the formula as well. You're absolutely right about readability but given the number of calculations and reporting that's required, it may well be unavoidable! — SQLGIT_GeekInTraining, Aug 29 '20 at 08:29

How can I add a new calculated column using a window function to my SQL query?

2 Answers2