SQL Server 2008, Sum two columns from different tables

Question

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;

Answer 1

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

Answer 2

In SQL Server, when performing an aggregate query, every column present in the SELECT clause must be present in the GROUP BY clause, or must have an aggregate function allowing values from multiple rows to be "rolled-up" into one row.

In MySQL, there is no such restriction due to it's quirkiness.

So, in SQL Server you need to either wrap (cash.sum_cash + bal.FORECAST) in an aggregate, such as SUM, or you need to add that to the GROUP BY clause. Similarly, the first subquery needs to have CMP added to the GROUP BY clause, or it needs to be aggregated.

You'll end up with something like this:

SELECT 
    cash.CMP as 'Name',
    cash.CODE as 'Code',
    SUM(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 CMP, CODE
        ) cash,
        (
        SELECT
            CODE,
            FORECAST
            FROM #TEST2
            where BALANCE_TYPE=-2
        ) bal 
GROUP BY cash.CMP, cash.CODE;