Calculated field error in select statement

[sentolga]

hi,

first of all, thank you very much for the amazing library. it saved my life. I cannot find a word to express my feelings.

I have a query as below that works fine in gsSQL

"Select d.account_name, d.account_id,count(d.device_id) as devicetotal,a.account_name,a.account_id,min(a.number_of_active_devices) as accounttotal
from d full join a on d.account_id=a.account_id group by d.account_name, d.account_id,a.account_name,a.account_id"

when I added a calculated field to the statement as follows I got an error :

"
Select d.account_name, d.account_id,count(d.device_id) as devicetotal,a.account_name,a.account_id,min(a.number_of_active_devices) as accounttotal, (devicetotal-accounttotal) as sss
from d full join a on d.account_id=a.account_id group by d.account_name, d.account_id,a.account_name,a.account_id
"

the error I got is: Invalid select field: (devicetotal-accounttotal) (line 2835)

I checked your examples and I see no difference. What is the problem? I Appreciate if you help me in this manner.

Thank you again,

Tolga

[demmings]

My field alias in the select is only used for display in column titles and so can't be used in calculated fields. I don't think that mySQL allows for this syntax either. I would try using a sub-query for this field (which is supported). You could also try to just use (count(d.device_id) - min(a.number_of_active_devices) ) as sss
See issue #11

[sentolga]

Thank you for getting back. Really appreciate it.

Your GS example works fine but it doesn't work on my file :

"Select d.account_name, d.account_id,count(d.device_id) as devicetotal,a.account_name,a.account_id,min(a.number_of_active_devices) as accounttotal, (count(d.device_id) - min(a.number_of_active_devices)) as sss
from d full join a on d.account_id=a.account_id group by d.account_name, d.account_id,a.account_name,a.account_id"

it gives the following error

Error: Invalid select field: (count(d.device_id) - min(a.number_of_active_devices)) (line 2835).

[demmings]

sentolga,

In my testing I have found several issues in and around JOIN and GROUP BY. For now doing a calculation on a aggregate field in a GROUP BY is failing. See: #54 , #55 and #56

[demmings]

Original issue raised was not being able to use a field alias in a calculation. This is not support in gsSQL() (or in mySQL as well). However, in this case, the fix should have been doing a calculated field that involved two aggregate functions in a GROUP BY statement. This calculation was not supported at that time. Now a basic calculation using aggregate fields are supported.

[sentolga]

Thank you so much fo your effort !