Aggregate totals in TSQL select error msg 116

Question

I have 5 balance fields possible for one loan records. I need to total these fields while excluding those columns per loan record that contain a specific code.

Here is an example of the data record, each total field has a corresponding code. I want to sum all totals and exclue the "X" code total. The code "X" can be attached to any total column, in this case it just happens to be at the end.

loan number | total 1 | total 2  | total 3 | total 4 |total 5 | code1 | code2 | code3 | code4 | code5
  123456789 |   12.50 | 1,950.43 |  750.00 |         | 275.98 | A     | B     | C     |       | X

Here is my subquery in an existing TSQL query:

, (SELECT [CMI ACCOUNT],
   [Unapplied Funds 1 - Balance] = SUM (CASE WHEN [UNAPPLIED code 1] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 1], 0) end 
        + CASE WHEN [UNAPPLIED code 2] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 2], 0) end 
        + CASE WHEN [UNAPPLIED code 3] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 3], 0) end 
        + CASE WHEN [UNAPPLIED code 4] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 4], 0) end 
        + CASE WHEN [UNAPPLIED code 5] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 5], 0) end)
FROM   CIT_UNAPPLIED
GROUP  BY [CMI ACCOUNT]  
)

Unfortunately, this gives me an error message stating:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

post all of your query, the piece you supplied cannot raise error described — avb, May 10 '14 at 00:58
This sub query returns multiple columns. Dup of http://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not. — qxg, May 10 '14 at 18:05

score 0 · Answer 1 · answered May 11 '14 at 22:04

The Error message suggests that the subquery is in the Select list or the WHERE clause

It should be part of from clause

SELECT 
b.[CMI ACCOUNT],
b.[Unapplied Funds 1 - Balance] 
FROM
(
SELECT [CMI ACCOUNT],
[Unapplied Funds 1 - Balance] = SUM (CASE WHEN [UNAPPLIED code 1] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 1], 0) end 
+ CASE WHEN [UNAPPLIED code 2] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 2], 0) end 
+ CASE WHEN [UNAPPLIED code 3] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 3], 0) end 
+ CASE WHEN [UNAPPLIED code 4] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 4], 0) end 
+ CASE WHEN [UNAPPLIED code 5] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 5], 0) end
FROM   CIT_UNAPPLIED
GROUP  BY [CMI ACCOUNT]  
) b

You can join that subquery to other Tables, views or subqueries as you would a normal table

SELECT 
a.[Account Name],
b.[CMI ACCOUNT],
b.[Unapplied Funds 1 - Balance] 
FROM
Another Table a
JOIN
(
SELECT [CMI ACCOUNT],
[Unapplied Funds 1 - Balance] = SUM (CASE WHEN [UNAPPLIED code 1] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 1], 0) end 
+ CASE WHEN [UNAPPLIED code 2] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 2], 0) end 
+ CASE WHEN [UNAPPLIED code 3] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 3], 0) end 
+ CASE WHEN [UNAPPLIED code 4] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 4], 0) end 
+ CASE WHEN [UNAPPLIED code 5] = 'L' THEN 0 ELSE ISNULL( [UNAPPLIED BALANCE 5], 0) end
FROM   CIT_UNAPPLIED
GROUP  BY [CMI ACCOUNT]  
) b
ON a.[CMI ACCOUNT]=b.[CMI ACCOUNT]

Depending on what you want the rest of your query to do

Aggregate totals in TSQL select error msg 116

1 Answers1