Hello, I'm trying to pull back data by ACCT_CD showing the total percentage of illiquid positions (p.udf_char1 in 4,5,7). I only want to group by f.ACCT_CD and total percentage calculated by the case clause, however I get error until I add columns p.UDF_CHAR1 and f.NET_ASSETS to the group by clause, but then it's summarized by fund and different categories of illiquid (UDF_CHAR1 4,5,7), I've also tried to group by f.ACCT_CD and the case statement, but it still wants me to add the UDF and Net Assets to group. When I add them though it break out each illiquid level..for example
ACCT_CD | Illiquid
9 5.05
9 5.35
9 1.01
Ideally I'd see one summarized row
ACCT_CD | Illiquid
9 11.41
Here is the sql that works but breaks out by illiquid
Select f.ACCT_CD,
(Case when p.UDF_CHAR1 in (4,5,7) then SUM(ABS(p.MKT_VAL_SOD))/f.NET_ASSETS else 0 end)*100 'Illiquid'
from cs_position p join CS_FUND f on p.acct_cd = f.acct_cd
where p.UDF_CHAR1 in (4,5,7)
Group by f.acct_cd, f.ACCT_NAME, p.UDF_CHAR1, f.NET_ASSETS
Thanks, any help would be appreciated.