CODEWITHSUNDEEP
sql
Nonagon
Nonagon

Reputation: 407

Getting a total of balances based on currency

So i am trying to get a sum of all balances for customers. What i want to do is get a total of all the values based on their Currency, for example if 3 customers had money in GBP like 1,2 and 3, the total price based on currency would be 6 as a total.

What I Have Tried

For the data to look visually appealing i have been able to use a Pivot function, making the Currency columns appear as Columns instead of rows like so:

SELECT *
FROM
(
    SELECT DISTINCT Customer AS Total 
    , Currency
    , Balances
    FROM #temptable
) SRC
PIVOT
(
    SUM(Balances)
    FOR [Currency] IN ([AUD],[CAD],[EUR],[GBP])
) AS P

but it returns as this:

        AUD CAD EUR GBP
CUS1    451     122
CUS2         213
CUS3         211   154
CUS4               123

I just want to have a row with the totals of all currencies

Upvotes: 0

Views: 328

Answers (2)

GarethD
GarethD

Reputation: 69769

If you want to use the pivot function, you must create your totals within the subquery before you can pivot them, which you can do with GROUPING SETS, for example,

-- DUMMY DATA
CREATE TABLE #temptable (Customer CHAR(4), Currency CHAR(3), Balances INT);
INSERT #TempTable (Customer, Currency, Balances)
VALUES 
    ('CUS1', 'AUD', 451), ('CUS1', 'EUR', 122), ('CUS2', 'CAD', 213), 
    ('CUS3', 'CAD', 211), ('CUS3', 'GBP', 154), ('CUS4', 'GBP', 123);

-- ACUTAL QUERY
SELECT ISNULL(CONVERT(VARCHAR(20), Customer), 'Column Total') AS Total 
, ISNULL(CONVERT(VARCHAR(20), Currency), 'RowTotal') AS Currency
, SUM(Balances) AS Balances
FROM #temptable t
GROUP BY GROUPING SETS ((Customer, Currency), (Customer), (Currency), ());

You get your totals (I have separated out each grouping set for ease of understanding)

Total           Currency    Balances
------------------------------------
-- Grouping set (Customer, Currency)
CUS1            AUD         451
CUS2            CAD         213
CUS3            CAD         211
CUS1            EUR         122
CUS3            GBP         154
CUS4            GBP         123
-- Grouping set (Customer)
CUS1            RowTotal    573     -- TOTAL FOR CUS1
CUS2            RowTotal    213     -- TOTAL FOR CUS1
CUS3            RowTotal    365     -- TOTAL FOR CUS1
CUS4            RowTotal    123     -- TOTAL FOR CUS1
-- Grouping set (Currency)
Column Total    AUD         451     -- TOTAL FOR AUD
Column Total    CAD         424     -- TOTAL FOR CAD
Column Total    EUR         122     -- TOTAL FOR EUR
Column Total    GBP         277     -- TOTAL FOR GBP
-- Grouping set ()
Column Total    RowTotal    1274    -- GRAND TOTAL

Then you do your pivot:

SELECT *
FROM
(
    SELECT ISNULL(CONVERT(VARCHAR(20), Customer), 'Column Total') AS Total 
    , ISNULL(CONVERT(VARCHAR(20), Currency), 'RowTotal') AS Currency
    , SUM(Balances) AS Balances
    FROM #temptable
    GROUP BY GROUPING SETS ((Customer, Currency), (Currency), (Customer), ())
) SRC
PIVOT
(
    SUM(Balances)
    FOR [Currency] IN ([AUD],[CAD],[EUR],[GBP],[RowTotal])
) AS P;

Which gives:

Total           AUD     CAD     EUR     GBP     RowTotal
------------------------------------------------------------
Column Total    451     424     122     277     1274
CUS1            451     NULL    122     NULL    573
CUS2            NULL    213     NULL    NULL    213
CUS3            NULL    211     NULL    154     365
CUS4            NULL    NULL    NULL    123     123

I wasn't sure which total you needed, so added both, but you should be able to adjust as necessary.

Realistically, a much simpler query is along the lines of what Gordon has suggested:

SELECT  Total = ISNULL(CONVERT(VARCHAR(20), Customer), 'Total'),
        AUD = SUM(CASE WHEN Currency = 'AUD' THEN Balances END),
        CAD = SUM(CASE WHEN Currency = 'CAD' THEN Balances END),
        EUR = SUM(CASE WHEN Currency = 'EUR' THEN Balances END),
        GBP = SUM(CASE WHEN Currency = 'GBP' THEN Balances END),
        RowTotal = SUM(Balances)
FROM    #temptable
GROUP BY Customer WITH ROLLUP;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269833

Just use aggregation:

SELECT SUM(CASE WHEN Currency = 'AUD' THEN Balance ELSE 0 END) as aud,
       SUM(CASE WHEN Currency = 'CAD' THEN Balance ELSE 0 END) as cad,
       SUM(CASE WHEN Currency = 'EUR' THEN Balance ELSE 0 END) as eur,
       SUM(CASE WHEN Currency = 'GBP' THEN Balance ELSE 0 END) as gbp
FROM #temptable;

If you can live with one row per currency, GROUP BY is simpler:

select currency, sum(balance)
from #temptable
group by currency;

Upvotes: 0

Related Questions