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;

• Lennart - Slava Ukraini: Somehow I doubt that your code-snippet works, what is cash.suma_saldo_cash for example?
• dmb: @Lennart Was a Typo it is sum_cash. I tried it in SQLfiddle and returns something that looks like what im looking for.
• jyao: If you can improve your question quality by providing some sample data population script for table Test1 and Test2, you will get quality answers. My gut feeling here is window function in sql server may help you.
• markp-fuso: re: error message "Column 'cash.sum_cash' is invalid..." : sql server appears to be enforcing the ANSI standard that (basically) says all non-aggregate columns must appear in the GROUP BY clause; since cash.sum_cash (and bal.FORECAST) are considered non-aggregates, ANSI says they (or rather the (cash....+ bal....) clause) should be in the GROUP BY clause; as Lennart's suggested in his answer, there doesn't appear to be a need for the top-level GROUP BY, but you do need a join clause between the cash and bal derived tables
• markp-fuso: what is the relationship between CMP and CODE? one-to-one? or can a single CODE value have multiple/different CMP values?

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

• dmb: This final code should be the accepted answer. Dropped the "JOIN ON" Clause and that was it. Thanks a lot.

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;