Scott Holtzman Scott Holtzman - 5 months ago 22
SQL Question

SQL Left Outer Join on Subquery

I am attempting to build a query that contains a left join subquery - based on the principles I learned in a previous question - that should pull similar data sets from two different tables. The goal is to compare volume data by

account || platform
to ensure that the stored procedure that creates one table from another is doing so correctly.

The idea is this:

Account || Product || T1Vol || T2Vol
abc AT 10 10
def RT 20 25
ghi OB 30


So with this example, the idea is to pull all accounts and products from T1 (the table the procedure acts on) and any accounts and products from T2 (the newly created table) where there is a match (so, Left Join on T1 = T2). (Ideally, everything will match perfectly, with no variance in T1 vs T2 vol and no nulls in T2 volume).

I wrote the following the query to accomplish this but its not quite working. The current error I get is
not a GROUP BY expression
- which I don't think is the real issue. I have been searching and with iterations to no avail.

The query is below. (To keep with the example,
T1 = OpStats
and
T2 = RegSplits
. Any help is much appreciated.

SELECT DTA.trading_code Account, OpStats.product_dwkey Platform, SUM(OpStats.risk_amount_adj)/1000000 OpStatsVol, RegSplits.Volume RegSplitsVol
FROM fact_trade_presplit_rollup OpStats
INNER JOIN dim_trading_accounts DTA ON OpStats.trading_dwkey=DTA.trading_dwkey
LEFT OUTER JOIN
( SELECT b.trading_Code Account, a.product_dwkey Platform, SUM(a.risk_amount_adj)/1000000 Volume
FROM fact_trade_rollup a
INNER JOIN dim_trading_accounts b on a.trading_dwkey=b.trading_dwkey
WHERE a.account_type IN('Customer','Taker')
AND a.date_key>='01-JAN-16'
AND a.date_key<='31-MAR-16'
AND a.daily_db_metric NOT IN ('Manual Treasury Volume ($B)', 'Manual Volume ($B)', 'HSBC-WL POMS (Internal) Volume ($B)','JPMC-WL Order Book (Internal) Volume ($B)')
AND (a.product_dwkey IN('RT','HWL') AND a.source_name<>'STP')
GROUP BY b.trading_code, a.product_dwkey ) RegSplits
ON (DTA.trading_code = RegSplits.Account) /* is it because I am trying to join DTA to the subquery */
WHERE OpStats.account_type IN('Customer','Taker')
AND OpStats.date_key>='01-JAN-16'
AND OpStats.date_key<='31-MAR-16'
AND OpStats.daily_db_metric NOT IN ('Manual Treasury Volume ($B)', 'Manual Volume ($B)', 'HSBC-WL POMS (Internal) Volume ($B)','JPMC-WL Order Book (Internal) Volume ($B)')
AND (OpStats.product_dwkey IN('RT','HWL') AND OpStats.source_name<>'STP')
GROUP BY DTA.trading_code, OpStats.product_dwkey;

Answer

The "Not group by expression" error is very easy to check.
Just compare SELECT expressions with GROUP BY expressions:

 SELECT DTA.trading_code Account, 
       OpStats.product_dwkey Platform, 
       SUM(OpStats.risk_amount_adj)/1000000 OpStatsVol, 
       RegSplits.Volume RegSplitsVol
FROM ......
......
GROUP BY DTA.trading_code, 
        OpStats.product_dwkey;

There are two elements in SELECT that are not in GROUP BY:

  1. SUM(OpStats.risk_amount_adj)/1000000 OpStatsVol
  2. RegSplits.Volume RegSplitsVol

The number 1 is OK - it's an aggregate function, it cannot be in GROUP BY.
The number 2 caused this error - it's not an aggregate function, and it is not listed in GROUP BY clause.