0

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;
  • 1
    Somehow I doubt that your code-snippet works, what is cash.suma_saldo_cash for example? – Lennart Aug 28 '17 at 16:48
  • @Lennart Was a Typo it is sum_cash. I tried it in SQLfiddle and returns something that looks like what im looking for. – dmb Aug 28 '17 at 16:51
  • 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. – jyao Aug 28 '17 at 17:19
  • 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 Aug 28 '17 at 17:20
  • what is the relationship between CMP and CODE? one-to-one? or can a single CODE value have multiple/different CMP values? – markp Aug 28 '17 at 17:22
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
  • This final code should be the accepted answer. Dropped the "JOIN ON" Clause and that was it. Thanks a lot. – dmb Aug 28 '17 at 18:18
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.