I have the following query:
SELECT
ncuenta,dcuenta,
(select SUM(saldo) where saldo>0) debe,
(select sum(-saldo) where saldo<0) haber
FROM tabla GROUP BY NCUENTA ORDER BY NCUENTA;
Which works for me but in warmserver version of mysql 5.7.19
and he does this:
*********************************************************
| ncuenta | dcuenta | debe | haber |
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::
| 101 | BANCOS | 16255.00 | NULL |
|_____________|_____________|_____________|_____________|
| 410-001 | INGRESOS | NULL | 5910.00 |
|_____________|_____________|_____________|_____________|
| 410-002 | OTROS | NULL | 12345.00 |
|_____________|_____________|_____________|_____________|
| 510-001 | COMPRAS | 1000.00 | NULL |
|_____________|_____________|_____________|_____________|
| 610-001 | SALARIOS | 1000.00 | NULL |
|_____________|_____________|_____________|_____________|
Which is exactly what I want.
But on the web server I have version 5.6.39 and when I do the query I get it:
Error SQL query: Documentation
SELECT nccount, account, (select SUM (balance) where balance> 0) must, (select sum (-salt) where balance < 0) have FROM table GROUP BY NCUENTA ORDER BY NCUENTA LIMIT 0, 25 MySQL has said: Documentation
1064 - Something is wrong in its near syntax 'where balance> 0) must, (select sum (-salt) where balance < 0) have FROM tables on line 1
I edit to give more information.
the database contains the following data:
... nccount ... account ......should ......to have ...... balance........ 101 .....BANKS .... 1234.00 ....... 0.00 .... 1234.00
.... 410-001 ...INCOME ....... 0.00 .... 1200.00 ...- 1200.00
........ 101 .....BANKS .... 1200.00 ....... 0.00 .... 1200.00
........ 101 .....BANKS ...... 12.00 ....... 0.00 ...... 12.00
and so on. when I do the query in the warmserver (I do not know why, but if the query works, I do not know if it will be the version or that)
SELECT ncuenta, dcuenta,
(select SUM (balance) where balance > 0) must,
(select sum (-salt) where balance < 0) have
FROM table GROUP BY NCUENTA ORDER BY NCUENTA
....... 101
....BANKS
...... 2446
......... 0
...... 2446
... 410-001
..INCOME
...... 0.00
... 1200.00
..- 1200.00
If I apply the same query with the suggestion that you are giving me then it makes me that the fields must have been equal or I add all the balance fields and it gives me the same result (if it serves me for something else but not for this consultation)