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;