Error converting data type nvarchar to numeric convert/cast

I suspect this has to do with reporting ClientName, which is a nvarchar data type. AUMid is nchar. I’m not sure how to work around this without changing the data types (which I prefer not to).

SELECT 
    Clients.ClientName, AUM.ManagementFee, 
    SUM(AUM.ManagementFee * AUM.AUM) AS Management_Fee
FROM
    AUM
JOIN 
    Clients ON AUM.AUMid = Clients.AUMid
GROUP BY 
   Clients.ClientName, AUM.ManagementFee

I need to display Clients.ClientName. It is linked to the AUM table via AUM.AUMid.

CREATE TABLE Clients 
(
    ClientID nvarchar(50),
    ClientName nvarchar(50)
    AccountID nchar(10),
    AUMid nchar(10)
);

CREATE TABLE AUM
(
    AUMid nchar(10),
    AUM nvarchar(max),
    ManagementFee(decimal(9,4)
);

Removing SUM(AUM.AUM * AUM.ManagementFee) AS Management_Fee allows the query to execute.

Answer

This is clearly causing your error:

SUM(AUM.ManagementFee * AUM.AUM) AS Management_Fee

because AUM.AUM is a string. That seems like a really bad choice of data types if you want to store a number. However, I suspect that you really just want a direct sum:

SELECT c.ClientName, 
       SUM(a.ManagementFee) AS Management_Fee
FROM Clients c JOIN
     AUM a
     ON a.AUMid = c.AUMid
GROUP BY c.ClientName;

On the other hand, the issue may be that AUM is a comma-separated list of numbers. If that is the case, my first and most sincere advice is to fix the data model. If you cannot do that for some reason, you can parse the string and keep your fingers crossed that it works:

SELECT c.ClientName, 
       SUM(a.ManagementFee * TRY_CONVERT(NUMERIC(38, 6), s.value)) AS Management_Fee
FROM Clients c JOIN
     AUM a
     ON a.AUMid = c.AUMid CROSS APPLY
     string_split(a.aum, ',') s
GROUP BY c.ClientName;