Hello,
I have a DB that contains the names of farmers who receive inputs. I need to know which farmer receives what amount of input according to this query:
SELECT farmer.Name ,Input.input, sum(input.amount) FROM Farmer
LEFT JOIN Input ON Input.GN=Farmer.GN GROUP BY by input.input, farmer.GN
This does not to be possible with a calculated field.
I have circumvented this now by creating a view and then having a table in AppGini with the same fields as the view - which works.
Is there any wat to do this in AppGini directly?
Thanks
Thomas
Group by
Re: Group by
Hi,
which is your calculated field and how is the calulaton done? You can try to reflect that in your query.
which is your calculated field and how is the calulaton done? You can try to reflect that in your query.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Group by
This is my query from the calculated field:
Select sum(`Input`.`amount`) from Farmer
LEFT JOIN `Input` ON `Input`.`GN`=`Farmer`.`GN`
WHERE `Farmer`.`GN`= %ID%
It will correctly give me the sum of inputs, but I still have to group by inputs to know the sum of each input.
Greetings
Thomas
Select sum(`Input`.`amount`) from Farmer
LEFT JOIN `Input` ON `Input`.`GN`=`Farmer`.`GN`
WHERE `Farmer`.`GN`= %ID%
It will correctly give me the sum of inputs, but I still have to group by inputs to know the sum of each input.
Greetings
Thomas
Re: Group by
HI Thomas,
maybe I'm not clear about your requirement. The calculated field needs to return one value. Can you please post the table structure and point to where the aggregated sum per input and farmer shall be stored.
maybe I'm not clear about your requirement. The calculated field needs to return one value. Can you please post the table structure and point to where the aggregated sum per input and farmer shall be stored.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
Re: Group by
Ok, I guess the basic problem is that AppGini works with tables only, while Mysql will generate a temporary table for a result of a query.
So, I can either work with views in the DB or create new calculated fields in the table for each instance like this:
Select sum(`Input`.`amount`) from Farmer
LEFT JOIN `Input` ON `Input`.`GN`=`Farmer`.`GN`
LEFT JOIN `input_list` ON `Input`.`input`=`input_list`.`id`
WHERE `Farmer`.`GN`= %ID% and `input_list`.`Id` = 1
and then 2,3,4,5 etc.
That does work and actually looks quite nice.
Greetings
Thomas
So, I can either work with views in the DB or create new calculated fields in the table for each instance like this:
Select sum(`Input`.`amount`) from Farmer
LEFT JOIN `Input` ON `Input`.`GN`=`Farmer`.`GN`
LEFT JOIN `input_list` ON `Input`.`input`=`input_list`.`id`
WHERE `Farmer`.`GN`= %ID% and `input_list`.`Id` = 1
and then 2,3,4,5 etc.
That does work and actually looks quite nice.
Greetings
Thomas