Sql-server – SQL Server 2008, Sum two columns from different tables

aggregatesql serversql-server-2008

I'm trying to return a table with only 3 columns: CMP, CODE and Totalization.

On the first table I need to Sum all the amount data and then group by CODE.

Then I query a second table and get the code and Forecast.

Finally, I need to sum the sum_cash with Forecast and group them by code.

The snippet below works for MySQL, but in SQL server, it keeps giving me this error:

"Column 'cash.sum_cash' is invalid in the select list because it is not contained in either an aggregate function or group by.

I'm really open to modify the code as long as I can get those 3 columns.

I'm Pretty sure the problem relies on the "Totalization" thing. But I'm no expert on SQL server, or any other SQL language, so I really need help for this one.

SELECT 
    cash.CMP as 'Name',
    cash.CODE as 'Code',
    (cash.sum_cash + bal.FORECAST) as 'Totalization'
From(
        Select 
            CMP,
            CODE,
            sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
            From TEST1
            where Nature=12
            GROUP BY CODE
        ) cash,
        (
        SELECT
            CODE,
            FORECAST
            FROM TEST2
            where BALANCE_TYPE=-2
        ) bal 
GROUP BY cash.CMP, cash.CODE;

Best Answer

You are probably getting a syntax error from:

Select 
        CMP,
        CODE,
        sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
From TEST1
where Nature=12
GROUP BY CODE

You probably need a full group by:

Select 
        CMP,
        CODE,
        sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
From TEST1
where Nature=12
GROUP BY CMP, CODE

or if CMP is functionally dependent on code you can apply an aggregate for CMP to make it valid:

Select 
        MAX(CMP) as CMP,
        CODE,
        sum(CASE when BUDGET in ('4','25') then AMOUNT else AMOUNT * -1 end) sum_cash
From TEST1
where Nature=12
GROUP BY CODE

Next thing I find peculiar is that you are doing a cross join between your two sub-selects, are they guaranteed to return exactly 1 row each? I would guess that what you are trying to do should be something like:

SELECT 
    cash.CMP as Name,
    cash.CODE,
    cash.sum_cash + bal.FORECAST as Totalization
From(
    Select MAX(CMP) as CMP, CODE
         , sum(CASE when BUDGET in ('4','25') 
                    then AMOUNT 
                    else AMOUNT * -1 
               end) sum_cash
    From TEST1
    where Nature=12
    GROUP BY CODE
) cash
JOIN (
    SELECT CODE, FORECAST
    FROM TEST2
    where BALANCE_TYPE=-2
) bal
    ON cash.code = bal.code
Related Question