Multiple Groupings inside a case statement & ordering

Question

I'm currently working on an aggregation stored proc and my final select looks like this:

SELECT 
        CASE 
            WHEN GROUPING(Custodian) = 1 
                THEN 'Grand Total'
            ELSE Custodian

        END AS Custodian

    ,   PortfolioID
    ,   PortfolioBaseCCY
    ,   [Date]
    ,   SUM(AmountTotalBaseEquiv) AS AmountTotalBaseEquiv
    ,   ExchangeRate
    ,   AmountTotalBaseEquivUSD
    ,   PortfolioNAVUSD
    ,   SUM(TotalCashPctNAV) AS TotalCashPctNAV 

FROM @ResultSet
WHERE TotalCashPctNAV > 5
GROUP BY Custodian
    ,   PortfolioID
    ,   PortfolioBaseCCY
    ,   [Date]
    ,   AmountTotalBaseEquiv
    ,   ExchangeRate
    ,   AmountTotalBaseEquivUSD
    ,   PortfolioNAVUSD 
    ,   TotalCashPctNAV WITH ROLLUP

HAVING GROUPING_ID(Custodian
    ,   PortfolioID
    ,   PortfolioBaseCCY
    ,   [Date]
    ,   AmountTotalBaseEquiv
    ,   ExchangeRate
    ,   AmountTotalBaseEquivUSD
    ,   PortfolioNAVUSD 
    ,   TotalCashPctNAV) IN (1,255,511)

ORDER BY ABS(TotalCashPctNAV) DESC

However i would like to add another grouping to the CASE statement, ie:

        CASE 
            WHEN GROUPING(Custodian) = 1 
                THEN 'Grand Total'
            WHEN GROUPING(PortfolioID) = 1
                THEN Custodian+''+'Total'
            ELSE Custodian

However it does not work as the second case doesn't return a value, why is this?

Also i would like to order the TotalCashPctNAV in the above select by:

ORDER BY ABS(TotalCashPctNAV) DESC

However this does not seem to be working. I would like it ordered so that it orders the ABS value descending between each portfolioID sub total.

Any help would be appreciated.

Narthring · Accepted Answer · 2012-09-11T14:36:10.493

Adding the second grouping to the CASE statement worked as I expected it to. The AmounttotalBaseEquiv and TotalCashPct columns were populated with values just like the Grand Total row had been, and the Custodian column seemed to be named appropriately for the individual Custodian totals.

I suspect the ordering issue has to do with the fact that the displayed column is actually the SUM of the TotalCashPctNAV, which has been aliased back to "TotalCashPctNAV". The ORDER BY statement is working against the actual TotalCashPctNAV column instead of the aliased SUM.

Interestingly if the ORDER BY statement doesn't use ABS, then the ordering is what I would expect it to be. I'm not sure offhand why the ABS causes it to order differently, since I was using all positive terms for my example.

Example:

DECLARE @ResultSet TABLE
(
    Custodian VARCHAR(10),
    PortfolioID INT,
    PortfolioBaseCCY INT,
    [Date] DATETIME,
    AmountTotalBaseEquiv NUMERIC(8,2),
    PortfolioNAVUSD NUMERIC(8,2),
    TotalCashPctNAV NUMERIC(8,2),
    ExchangeRate NUMERIC(8,2),
    AmountTotalBaseEquivUSD NUMERIC(8,2)
)

INSERT INTO @ResultSet SELECT 'Anne', 1, 1, '6/1/2012', '600.00', '643.45', '3.78', '2.00', '353.00'
INSERT INTO @ResultSet SELECT 'Bob', 2, 1, '6/13/2012', '745.00', '9.42', '36.70', '1.70', '353.00'
INSERT INTO @ResultSet SELECT 'Carl', 3, 1, '6/27/2012', '488.00', '9875.99', '6.60', '1.80', '353.00'
INSERT INTO @ResultSet SELECT 'Doug', 4, 1, '6/29/2012', '87.00', '45.98', '69.12', '2.10', '353.00'
INSERT INTO @ResultSet SELECT 'Elmer', 5, 1, '6/30/2012', '775.00', '78.47', '69.78', '2.41', '353.00'
INSERT INTO @ResultSet SELECT 'Bob', 6, 1, '6/30/2012', '775.00', '78.47', '69.78', '2.41', '353.00'

SELECT 
        CASE 
            WHEN GROUPING(Custodian) = 1 
                THEN 'Grand Total'
            WHEN GROUPING(PortfolioID) = 1
                THEN Custodian+''+'Total'

            ELSE Custodian

        END AS Custodian

    ,   PortfolioID
    ,   PortfolioBaseCCY
    ,   [Date]
    ,   SUM(AmountTotalBaseEquiv) AS AmountTotalBaseEquiv
    ,   ExchangeRate
    ,   AmountTotalBaseEquivUSD
    ,   PortfolioNAVUSD
    ,   SUM(TotalCashPctNAV) AS TotalCashPctNAVSUM --I appended SUM to the alias

FROM @ResultSet
WHERE TotalCashPctNAV > 5
GROUP BY Custodian
    ,   PortfolioID
    ,   PortfolioBaseCCY
    ,   [Date]
    ,   AmountTotalBaseEquiv
    ,   ExchangeRate
    ,   AmountTotalBaseEquivUSD
    ,   PortfolioNAVUSD 
    ,   TotalCashPctNAV WITH ROLLUP

HAVING GROUPING_ID(Custodian
    ,   PortfolioID
    ,   PortfolioBaseCCY
    ,   [Date]
    ,   AmountTotalBaseEquiv
    ,   ExchangeRate
    ,   AmountTotalBaseEquivUSD
    ,   PortfolioNAVUSD 
    ,   TotalCashPctNAV) IN (1,255,511)
ORDER BY ABS(TotalCashPctNAV) DESC  --works with TotalCashPctNAV but not TotalCashPctNAVSUM
--ORDER BY TotalCashPctNAV DESC  --works as initially expected

Multiple Groupings inside a case statement & ordering

1 Answers1