1

I am trying to use an aggregate function in MySQL to return the balance of an account. However I keep getting an error an it's driving me nuts!

SELECT cast(sum((number_sold - number_bought) * price) as unsigned) as 'Balance'
    FROM transactions

This returns:

#2014 - Commands out of sync; you can't run this command now

Here's an SQLfiddle: http://sqlfiddle.com/#!2/26297/1

Any help appreciated!

user2761030
  • 1,385
  • 2
  • 20
  • 33
  • 2
    That typically happens if you have rows remaining to be fetched and you attempt to issue a new query without first closing the previously opened cursor/freeing result set. This code must be executed in context of some other statements. Can you post more for context? – Michael Berkowski Sep 24 '14 at 20:09
  • Thanks @MichaelBerkowski This is the only code that I'm trying to run... SQL fiddle is currently down, but will post something there when it's back up! – user2761030 Sep 24 '14 at 20:13
  • Are you running this in application code (PHP, Ruby, whatever)? If so, there may be an earlier result set open that needs to be freed. This single SQL statement alone couldn't cause the 2014 error. – Michael Berkowski Sep 24 '14 at 20:16
  • I'm running it in PHPMyAdmin, but strangely I get a different error when I try running it in a terminal via SSH: `ERROR 1690 (22003): BIGINT UNSIGNED value is out of range` – user2761030 Sep 24 '14 at 20:20
  • I've now added an SQLfiddle, which also shows the BIGINT UNSIGNED error. – user2761030 Sep 24 '14 at 20:33
  • That appears to be because you have some for which `number_bought > number_sold` which becomes negative after subtraction, and negatives are out of range for unsigned. If I remove the `UNISGNED` option from those columns it works: http://sqlfiddle.com/#!2/86549/4 – Michael Berkowski Sep 24 '14 at 20:43

1 Answers1

1

As mentioned by Xepoch here,

If either the left-hand side or the right-hand side of the subtraction operator is unsigned, the result is unsigned as well. You can change this by setting the NO_UNSIGNED_SUBTRACTION SQL mode.

Alternatively, you can also explicitly cast your unsigned values to be signed bigint values and then do the subtraction.

Thus,

SELECT (cast(number_sold AS signed) - cast(number_bought AS signed)) * price AS 'Balance'
FROM transactions
Community
  • 1
  • 1
Yigitalp Ertem
  • 1,901
  • 24
  • 27