Discussion on: SQL Query Inner Join for SUM Amount

[Jered Sanchez]

Hey hey. This may not be the cleanest solution but it will get what you're looking for. What I would do is create an outer aggregate query of a subquery of case statements for each Item.

Here's your query:

select
    OpenDate,
    CloseDate,
    Account,
    LastName,
    FirstName,
    SUM(Amount) as Amount,
    SUM(Item1) AS Item1,
    SUM(Item2) AS Item2,
    SUM(Item3) AS Item3,
    SUM(Item4) AS Item4,
    SUM(Item5) AS Item5,
    SUM(Item6) AS Item6,
    SUM(Item7) AS Item7,
    SUM(Item8) AS Item8

FROM (

    select 
        OpenDate,
        CloseDate,
        GLAccountId as Account,
        LastName,
        FirstName,
        t3.ExepnseTypeId,
        t3.Amount as Amount,
        CASE WHEN t3.ExepnseTypeId = 'Item 1' THEN t3.Amount END AS Item1,
        CASE WHEN t3.ExepnseTypeId = 'Item 2' THEN t3.Amount END AS Item2,
        CASE WHEN t3.ExepnseTypeId = 'Item 3' THEN t3.Amount END AS Item3,
        CASE WHEN t3.ExepnseTypeId = 'Item 4' THEN t3.Amount END AS Item4,
        CASE WHEN t3.ExepnseTypeId = 'Item 5' THEN t3.Amount END AS Item5,
        CASE WHEN t3.ExepnseTypeId = 'Item 6' THEN t3.Amount END AS Item6,
        CASE WHEN t3.ExepnseTypeId = 'Item 7' THEN t3.Amount END AS Item7,
        CASE WHEN t3.ExepnseTypeId = 'Item 8' THEN t3.Amount END AS Item8

    from dbo.Associates t1
    inner join dbo.Relocations t2
    on t1.AssociateId = t2.AssociateId
    inner join dbo.Expenses t3
    on t2.RelocationId = t3.RelocationId) AS [Data]

group by
    OpenDate,
    CloseDate,
    Account,
    LastName,
    FirstName

order by Account