I am trying to create a view in SQL Server that renders columns from 3 tables and that creates 4 NEW columns. These new columns use the SQL "Case...When" syntax to conditionally render the SUM of existing fields depending on the values of an adjacent column [Portfolio Report Category] in the same table.
The objective is to display a single account's total market value for 4 distinct asset categories. Right now an [Account Number] can have MANY records with MANY [Investment Amount] for a single [Portfolio Report Category]. So obviously this view should allow an [Account Number] to have only ONE record with 4 summed monetary values that meet the "Case...When" conditions.
I have read similar threads, but none that addresses an aggregate method after the "Then" keyword in the conditional block. When I place the Sum() before "Case" and remove it after the "Then," there is no error but the calculations are wrong: In many cases, they are much higher. I was under the impression that a simple Try_Convert to money would be sufficient, and indeed if I execute SUM(Try_Convert(money, [Market_Value]) Where [Account Number] = 'x', the result is correct. But not when I'm creating the view.
Here is the error when testing the following code with SELECT:
Column 'KTHoldings.Portfolio Report Category' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Further below is the KTHoldings table returning all records for a single account. I basically need the [Other Total Value] column of my view to sum its [Market Value] data.
CREATE VIEW VW_KTAccountsADR AS (
SELECT adr.[Account Number], adr.[Account_ Display Name], adr.[Account Category Code], adr.[Division Code], adr.[Division Name], adr.[Market Value Amount],
CASE WHEN kth.[Portfolio Report Category] = '705' THEN SUM((TRY_CONVERT(money, kth.[Market Value]))) ELSE '0.00' END AS [Crypto Total Value],
CASE WHEN kth.[Portfolio Report Category] = '691' THEN SUM((TRY_CONVERT(money, kth.[Market Value]))) ELSE '0.00' END AS [Precious Metal Total Value],
CASE WHEN kth.[Portfolio Report Category] IN ('010', '011', '020', '025', '030') THEN SUM((TRY_CONVERT(money, kth.[Market Value]))) ELSE '0.00' END AS [Stock Total Value],
CASE WHEN kth.[Portfolio Report Category] NOT IN ('010', '011', '020', '025', '030', '691', '705') THEN SUM((TRY_CONVERT(money, kth.[Market Value]))) ELSE '0.00' END AS [Other Total Value],
ktc.[Available Cash Amount] AS [CASH TOTAL]
FROM KTAccountsADR adr
INNER JOIN KTHoldings kth
ON adr.[Account Number] = kth.[Account Number]
INNER JOIN KTCash ktc
ON adr.[Account Number] = ktc.[Account Number]
GROUP BY adr.[Account Number], adr.[Account_ Display Name], adr.[Account Category Code], adr.[Division Code], adr.[Division Code], adr.[Division Name], adr.[Market Value Amount], ktc.[Available Cash Amount]
)
KTAccountsADR Table
Account Number Display Name Division Code Market Value Amount
07007835 Frank C Thomas Roth IRA 27 390410.98
07007835 Frank C Thomas Roth IRA 27 390410.98
07007835 Frank C Thomas Roth IRA 27 390410.98
07007835 Frank C Thomas Roth IRA 27 390410.98
001000 Carl S Sykes Roth IRA 27 196338.1292
001000 Carl S Sykes Roth IRA 27 196338.1292
001000 Carl S Sykes Roth IRA 27 196338.1292
KTHoldings Table
Account Number Display Name Market Value Portfolio Report Category
001000 Carl S Sykes Roth IRA 9998.4792 600
001000 Carl S Sykes Roth IRA 43467.09 600
001000 Carl S Sykes Roth IRA 84524.71 600
KTCash Table
Account Number Available Cash Amount
001000 58347.85
You need to put your case inside your sum:
SUM(CASE WHEN kth.[Portfolio Report Category] = '705' THEN TRY_CONVERT(money, kth.[Market Value]) ELSE '0.00' END) AS [Crypto Total Value],
Here is a working example:
declare @Adr table ([Account Number] varchar(6), [Account_ Display Name] varchar(64), [Account Category Code] varchar, [Division Code] varchar, [Division Name] varchar, [Market Value Amount] money);
declare @Kth table ([Account Number] varchar(6), [Portfolio Report Category] varchar(3), [Market Value] money);
declare @Ktc table ([Account Number] varchar(6), [Available Cash Amount] money);
insert into @Adr ([Account Number], [Account_ Display Name])
values ('001000', 'Carl S Sykes Roth IRA');
insert into @Kth ([Account Number], [Market Value], [Portfolio Report Category])
values ('001000', 9998.4792, '600'),
('001000', 43467.09, '600'),
('001000', 84524.71, '600');
insert into @Ktc ([Account Number])
values ('001000');
SELECT adr.[Account Number], adr.[Account_ Display Name], adr.[Account Category Code], adr.[Division Code], adr.[Division Name], adr.[Market Value Amount]
, SUM(CASE WHEN kth.[Portfolio Report Category] = '705' THEN (TRY_CONVERT(money, kth.[Market Value])) ELSE 0 END) AS [Crypto Total Value]
, SUM(CASE WHEN kth.[Portfolio Report Category] = '691' THEN (TRY_CONVERT(money, kth.[Market Value])) ELSE 0 END) AS [Precious Metal Total Value]
, SUM(CASE WHEN kth.[Portfolio Report Category] IN ('010', '011', '020', '025', '030') THEN (TRY_CONVERT(money, kth.[Market Value])) ELSE 0 END) AS [Stock Total Value]
, SUM(CASE WHEN kth.[Portfolio Report Category] NOT IN ('010', '011', '020', '025', '030', '691', '705') THEN (TRY_CONVERT(money, kth.[Market Value])) ELSE 0 END) AS [Other Total Value]
, ktc.[Available Cash Amount] AS [CASH TOTAL]
FROM @Adr adr
INNER JOIN @Kth kth ON adr.[Account Number] = kth.[Account Number]
INNER JOIN @Ktc ktc ON adr.[Account Number] = ktc.[Account Number]
GROUP BY adr.[Account Number], adr.[Account_ Display Name], adr.[Account Category Code], adr.[Division Code], adr.[Division Code], adr.[Division Name], adr.[Market Value Amount], ktc.[Available Cash Amount];
Which returns (un-necessary columns removed):
Account Number Crypto Total Value Precious Metal Total Value Stock Total Value Other Total Value CASH TOTAL
001000 0.00 0.00 0.00 137990.2792 NULL
Edit: As you have now added sample data, the reason you were getting incorrect values is because you had duplicate rows in your KTAccountsADR which then multiple the rows in the KTHoldings table. Resolve the duplicates and you will get the correct values when using case inside sum.
Note, its best practice to ensure you are returning the same datatype from all branches of a case expression.