Group and Sum problems

  • I hope this is the correct forum. I have a simple table with a few columns. In a view, I want to sum and group various rows into a new columns based on values form the category column.

    Example Table:

    SourceCategory Currency

    Source 1CAA 18.00

    Source 1DAA 15.00

    Source 1EAA 12.00

    Source 1PRI 10.00

    Source 1 BRI 15.00

    Source 1AAS 12.00

    Source 1BSS 10.00

    Source 1CSS 5.00

    In my View I want to take all the CAA, DAA, EAA and PRI currency values and Sum them and put them in a column called Type 1

    I want to take all the BRI, AAS, BSS and CSS currency values and put them in a new column called Type 2.

    The result being:

    Example View:

    SourceType 1 (CAA,DAA,EAA,PRI Categories)Type 2 (BRI,AAS,BSS,CSS Categories)

    Source 155.00 (this is the sum) 42.00 (this is the sum)

    I hope this makes sense. Can anyone help me out? I'm trying different methods of grouping and sums, but I can't get these value totals into new columns.

    Thanks

  • This?

    DECLARE @Tab TABLE

    (

    Src VARCHAR(10)

    ,Category VARCHAR(3)

    ,Amount DECIMAL(10,2)

    )

    INSERT INTO @Tab

    SELECT 'Source 1' , 'CAA' ,18.00

    UNION ALL SELECT 'Source 1' , 'DAA' ,15.00

    UNION ALL SELECT 'Source 1' , 'EAA' ,12.00

    UNION ALL SELECT 'Source 1' , 'PRI' ,10.00

    UNION ALL SELECT 'Source 1' , 'BRI' ,15.00

    UNION ALL SELECT 'Source 1' , 'AAS' ,12.00

    UNION ALL SELECT 'Source 1' , 'BSS' ,10.00

    UNION ALL SELECT 'Source 1' , 'CSS' ,5.00

    ; WITH CTE AS

    (

    SELECT T.Src

    , 'CAA' = MAX ( CASE WHEN T.Category = 'CAA' THEN T.Amount ELSE 0 END )

    , 'DAA' = MAX ( CASE WHEN T.Category = 'DAA' THEN T.Amount ELSE 0 END )

    , 'EAA' = MAX ( CASE WHEN T.Category = 'EAA' THEN T.Amount ELSE 0 END )

    , 'PRI' = MAX ( CASE WHEN T.Category = 'PRI' THEN T.Amount ELSE 0 END )

    , 'BRI' = MAX ( CASE WHEN T.Category = 'BRI' THEN T.Amount ELSE 0 END )

    , 'AAS' = MAX ( CASE WHEN T.Category = 'AAS' THEN T.Amount ELSE 0 END )

    , 'BSS' = MAX ( CASE WHEN T.Category = 'BSS' THEN T.Amount ELSE 0 END )

    , 'CSS' = MAX ( CASE WHEN T.Category = 'CSS' THEN T.Amount ELSE 0 END )

    FROM @Tab T

    GROUP BY T.Src

    )

    SELECT C.Src

    ,Type1 = C.CAA + C.DAA + C.EAA + C.PRI

    ,Type2 = C.AAS +C.BRI + C.BSS + C.CSS

    FROM CTE C

  • I will try that, thank you. I think Union is what I was looking for.

  • defiantclass (3/23/2012)


    I will try that, thank you. I think Union is what I was looking for.

    Union ? Why union? I gave a cross-tab type query. The UNION ALL is for populating the sample data..

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply