Help with SQL - I get column with 0 for each record

[qbecerra]

Hi guys
1º lemme explain situation:
there're clients who buy at a shop. there're promotions in shop (can be normal or special). one promotion says: "for each u$s 100 you buy, you get 1 point". client can exchange points by gifts.

2º Take a look at this image

http://img24.echo.cx/img24/2770/errorsql19dy.jpg

some terms translated:

CLIENTE.NOMBRE = CLIENT.NAME
FACTURACION.PUNTOS = PURCHASE.POINTSOBTAINED
FACTURACION.MONTO = PURCHASE.AMMOUNTOFMONEY
CANJE.TOTALPUNTOS = EXCHANGE.TOTALPOINTS
espromoesp = ISPROMOTIONESPECIAL
PUNTOS_RESTANTES = TOTALPOINTS_LEFT
PUNTOS_CANJEADOS = POINTS_EXCHANGED
CLIENTE.IDPROMO = CLIENTE.IDPROMOTION

**************************************************
**************************************************

does any1 know why i get 0 in PUNTOS_RESTANTES ?
I should get this difference:

PURCHASE.POINTSOBTAINED - EXCHANGE.TOTALPOINTS

I mean, the points client got in all purchases made
minus all the points he exchanged

**************************************************
**************************************************

If there's yet something you don't understand (it is in spanish) please tell me...

THANKS A LOT FOR YOUR HELP

 

[rtmpxr]

Try to change the GROUP BY clause into:
GROUP BY 1,2

 

[qbecerra]

GROUP BY 1,2 ?

I get syntax error...

Anyway, I'm getting same result if I change the order of "appearance" in the group by clause...

any ideas ? thanks a lot 4 helping..

the SQL engine I use is

http://www.nexusdb.com

and it is compatible with ISO SQL standards, etc.

 

[rtmpxr]

You can group by ordinal position but probably nexusdb doesn't accept this syntax. I did a mistake, anyway, indicating that. Because looking again on your query I saw that your second term in the GROUP BY clause is an aggregate function. You cannot do such thing. And I'm pretty sure that PUNTOS_RESTANTES is a field in your database which explains the results.

 

[qbecerra]

No...

PUNTOS_RESTANTES is not a field in my DB, this is a field in the query...

"... SUM(FACTURACION.PUNTOS) - SUM(CANJE.TOTALPUNTOS) AS PUNTOS_RESTANTES, ..."

I had to put PUNTOS_RESTANTES in group by clause because i get error if i do not do so...

Note:
I also replaced SUM(FACTURACION.PUNTOS) by 10000 to see if the query was substracting, but instead 0 i got 10000 for each record...

After this, i replaced SUM(CANJE.TOTALPUNTOS) by 1000 and i got -1000 in each record...

So, it seems nexus is not taking this values... none of them....

I'm becoming nuts with this... Thanks a lot 4 helping !!

 

[rtmpxr]

Look again at the GROUP BY syntax, but as far I know, you cannot use an aggregate function in a GROUP BY clause.
At least this is what is said in the SQL syntax language for Firebird 1.5 which is pretty close to the ANSI92 standard.