Hello, I have SQL that I use to find the percentage of net assets that are not illiquid NON_MKTBL is N and have seven or greater "Days to Exit". It's easy enough at the individual security level with this: Select p.ACCT_CD, f.ACCT_NAME, s.TICKER, s.SEC_NAME, p.MKT_VAL_SOD, f.NET_ASSETS, s.UDF_FLOAT1, (case when coalesce(S.UDF_FLOAT2,S.UDF_FLOAT1)>0 then p.QTY_SOD/(coalesce(S.UDF_FLOAT2,S.UDF_FLOAT1)/3) else 0 end) as 'EXIT_DAYS', (p.MKT_VAL_SOD)/(f.NET_ASSETS)*100 'Illiquid > 7' from CS_POSITION p join CSM_SECURITY s on p.SEC_ID = s.SEC_ID join cs_fund f on p.ACCT_CD = f.ACCT_CD join CS_FUND_CONFIG fc on f.ACCT_CD = fc.CHILD_ACCT_CD where (s.NON_MKTBL = 'N' or s.NON_MKTBL is null) and s.SEC_TYP_CD in ('COM','ADR','FSTK') and fc.PARENT_ACCT_CD in ('SEP','FUNDS') and p.ACCT_CD = '8' order by 'Exit_DAYS' desc, s.TICKER But I need this rolled up to the Account level, not individual security level so I added group by `Group by p.ACCT_CD, f.ACCT_NAME, f.NET_ASSETS` and finally I need to filter by those securities with exit days greater than 7. I attempted to include Having statement after the group by ` Having SUM(case when coalesce(S.UDF_FLOAT2,S.UDF_FLOAT1)>0 then p.QTY_SOD/(coalesce(S.UDF_FLOAT2,S.UDF_FLOAT1)/3) else 0 end) > 7` but that only applies to the Account level since it is look at the group by clause. How can I filter by greater than 7 day at security level before the grouping?
You mentioned the "individual security level". If that's your lowest level, you can just add your condition to the WHERE clause without any summing. Please let me know if that's not the case (i.e. if you need to group/sum at the security level and then group at the account level).