Trying to SUM columns created in the select statement with columns named in the select

Question

I am using the following code and am looking to have column Total_Bank_Amount and Prog_Variance SUMed from the SUMs in the previous CASEs.

SELECT 
    b.Date, 
    SUM(p.Prog_Amount) AS Prog_Amount, 
    SUM(o.Credit_Amt) AS Outstanding, 
    CASE 
       WHEN b.Bank_Name Like '%Corp%' 
          THEN SUM(b.Credit_Amt) 
    END AS TMP_Amount,
    CASE 
       WHEN b.Bank_Name Like '%Cleo%' 
          THEN SUM(b.Credit_Amt) 
    END AS Cleo_Amount, 
    CASE 
       WHEN b.Bank_Name Like '%NY Bank%' 
          THEN SUM(b.Credit_Amt)
    END AS NY_Amount, 
    CASE 
       WHEN b.Bank_Name Like '%MA%' 
          THEN SUM(b.Credit_Amt) 
    END AS MA_Amount, 
    CASE 
       WHEN b.Bank_Name Like '%CT%' 
          THEN SUM(b.Credit_Amt) 
    END AS CT_Amount, 
    CASE 
       WHEN b.Bank_Name Like '%NY_SS%' 
          THEN SUM(b.Credit_Amt)
    END AS NY_SS_Amount, 
    CASE 
       WHEN b.Bank_Name LIKE '%VC_SS%' 
          THEN SUM(b.Credit_Amt)
    END AS VC_SS_Amount,
    (TMP_Amount + Cleo_Amount + NY_Amount + MA_Amount + CT_Amount + NY_SS_Amount + VC_SS_Amount) AS Total_Bank_Amount,
    (Prog_Amount + Outstanding - TMP_Amount - Cleo_Amount - NY_Amount - MA_Amount - CT_Amount - NY_SS_Amount - VC_SS_Amount) AS Prog_Variance
FROM 
    vw_Prog_Reference_Summary p
RIGHT JOIN 
    Bank_Detail b ON p.Bank_Reference_Number = b.Bank_Reference 
LEFT JOIN 
    vw_Outstanding_Form o ON b.Bank_Reference = o.Bank_Reference
LEFT JOIN 
    Exclusion e ON b.Bank_Reference = e.Exclude
WHERE 
    e.Exclude IS NULL
GROUP BY 
    b.Bank_Name, b.Date

The issue is that these are not created by the time they parse so how do you get around this? Research has me coming up with nothing =(. Thanks.

score 1 · Accepted Answer · edited May 23 '17 at 10:27

1

Unfortunately you cannot reference a column alias within the same select statement. Typically the work around for this is to make your core query a sub query and then reference the column aliases OR you can repeat the same logic again in the calculation (so you would repeat all of those case and sum statements in the aggregate calculation again).

Below link for reference since this has been asked before: Reference an alias elsewhere in the SELECT list

edited May 23 '17 at 10:27

Community

1
1

answered Sep 06 '16 at 20:02

Zi0n1

594
2
5
16

@Zi0n1This worked. The orginal link for some reason I still could not grasp it but I did how ever do a bit more looking and found the same type of answer just a bit better layed out I guess: [link](http://stackoverflow.com/questions/11182339/reference-alias-calculated-in-select-in-where-clause) – KRM Sep 07 '16 at 13:10

score 0 · Answer 2 · answered Sep 06 '16 at 20:01

Try something like this:

SELECT
    (TMP_Amount + Cleo_Amount + NY_Amount + MA_Amount + CT_Amount + NY_SS_Amount + VC_SS_Amount) AS Total_Bank_Amount,
    (Prog_Amount + Outstanding - TMP_Amount - Cleo_Amount - NY_Amount - MA_Amount - CT_Amount - NY_SS_Amount - VC_SS_Amount) AS Prog_Variance
FROM (
    SELECT b.Date, Sum(p.Prog_Amount) AS Prog_Amount, Sum(o.Credit_Amt) AS Outstanding, 
        CASE WHEN b.Bank_Name Like '%Corp%' THEN SUM(b.Credit_Amt) END AS TMP_Amount,
        CASE WHEN b.Bank_Name Like '%Cleo%' THEN SUM(b.Credit_Amt) END AS Cleo_Amount, 
        CASE WHEN b.Bank_Name Like '%NY Bank%' THEN SUM(b.Credit_Amt)END AS NY_Amount, 
        CASE WHEN b.Bank_Name Like '%MA%' THEN SUM(b.Credit_Amt) END AS MA_Amount, 
        CASE WHEN b.Bank_Name Like '%CT%' THEN SUM(b.Credit_Amt) END AS CT_Amount, 
        CASE WHEN b.Bank_Name Like '%NY_SS%' THEN SUM(b.Credit_Amt)END AS NY_SS_Amount, 
        CASE WHEN b.Bank_Name Like '%VC_SS%' THEN SUM(b.Credit_Amt)END AS VC_SS_Amount
    FROM vw_Prog_Reference_Summary p
        RIGHT JOIN Bank_Detail b ON p.Bank_Reference_Number = b.Bank_Reference 
        LEFT JOIN vw_Outstanding_Form o ON b.Bank_Reference = o.Bank_Reference
        LEFT JOIN Exclusion e ON b.Bank_Reference = e.Exclude
    WHERE e.Exclude Is Null
    GROUP BY b.Bank_Name, b.Date
) t

I moved the old query to a subquery so that the values that you want are available. I also moved the summations for Total_Bank_Amount and Prog_Variance outside.

Gordon Linoff · Answer 3 · 2016-09-08T02:10:04.750

0

I think you are looking for conditional aggregation:

SELECT b.Date, Sum(p.Prog_Amount) AS Prog_Amount, Sum(o.Credit_Amt) AS Outstanding, 
       SUM(CASE WHEN b.Bank_Name Like '%Corp%' THEN b.Credit_Amt END) AS TMP_Amount,
      . . .
FROM . . .
GROUP BY b.Date;

That is, you want the CASE as an argument to the SUM(), not the other way around.

This assumes (reasonably) that you don't actually want a separate row for each bank, but only the aggregated information.

edited Sep 08 '16 at 02:10

answered Sep 06 '16 at 20:04

Gordon Linoff

1,242,037
58
646
786

this was also a good catch I was looking to Sum the entire case. I updated my code with that as well. – KRM Sep 07 '16 at 13:11

score 0 · Answer 4 · answered Sep 06 '16 at 20:04

Read up on Rollup, Cube and Grouping Sets. This will give you the sums in the same select (if that's what you want), but you will have to account for the NULL values.

Alternately, you can use a UNION with a WITH like this:

With SummaryData As
(
    SELECT 
        b.Date, Sum(p.Prog_Amount) AS Prog_Amount, Sum(o.Credit_Amt) AS Outstanding, 
        CASE WHEN b.Bank_Name Like '%Corp%' THEN SUM(b.Credit_Amt) END AS TMP_Amount,
        CASE WHEN b.Bank_Name Like '%Cleo%' THEN SUM(b.Credit_Amt) END AS Cleo_Amount, 
        CASE WHEN b.Bank_Name Like '%NY Bank%' THEN SUM(b.Credit_Amt)END AS NY_Amount, 
        CASE WHEN b.Bank_Name Like '%MA%' THEN SUM(b.Credit_Amt) END AS MA_Amount, 
        CASE WHEN b.Bank_Name Like '%CT%' THEN SUM(b.Credit_Amt) END AS CT_Amount, 
        CASE WHEN b.Bank_Name Like '%NY_SS%' THEN SUM(b.Credit_Amt)END AS NY_SS_Amount, 
        CASE WHEN b.Bank_Name Like '%VC_SS%' THEN SUM(b.Credit_Amt)END AS VC_SS_Amount,
        (TMP_Amount + Cleo_Amount + NY_Amount + MA_Amount + CT_Amount + NY_SS_Amount + VC_SS_Amount) AS Total_Bank_Amount,
        (Prog_Amount + Outstanding - TMP_Amount - Cleo_Amount - NY_Amount - MA_Amount - CT_Amount - NY_SS_Amount - VC_SS_Amount) AS Prog_Variance
    FROM vw_Prog_Reference_Summary p
    RIGHT JOIN Bank_Detail b 
        ON p.Bank_Reference_Number = b.Bank_Reference 
    LEFT JOIN vw_Outstanding_Form o 
        ON b.Bank_Reference = o.Bank_Reference
    LEFT JOIN Exclusion e 
        ON b.Bank_Reference = e.Exclude
    WHERE e.Exclude Is Null
    GROUP BY b.Bank_Name, b.Date
)

Select *
From SummaryData 

Union All

SELECT
    Date,
    0 Prog_Amount,
    0 Outstanding,
    0 TMP_Amount,
    0 Cleo_Amount,
    0 NY_Amount,
    0 MA_Amount,
    0 CT_Amount,
    0 NY_SS_Amount,
    0 VC_SS_Amount,
    Sum (Total_Bank_Amount) Total_Bank_Amount,
    Sum (Prog_Variance) Prog_Variance
FROM SummaryData
Group By
    Date

this gave me the same issues as my current script. Zi0n1 has the correct route for this one. — KRM, Sep 07 '16 at 13:12

Trying to SUM columns created in the select statement with columns named in the select

4 Answers4