Calculated field from 3rd table

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
gatenet
Veteran Member
Posts: 45
Joined: 2016-07-26 09:34
Location: Greece
Contact:

Calculated field from 3rd table

Post by gatenet » 2021-05-31 19:17

Hello to all the team here, quick question
I have 3 tables:
1. is table subscriptions that has some fields and a total price field that I want to be calculated
2. Subscription Services that has the fields: id, subscription id (lookup field (id) from table subscriptions) Service and price (both lookup fields from the 3rd table)
3. Services that has the fields service name and price

So I want the field Total price from Table Subscriptions to be the SUM of the records that are in the subscription services table related to the specific subscription id, the problem is that the SQL Query is taking the id of the second table and not the price. Please help me because I don't know how to make SQL Queries

The Query helper made me thiw query but I think that something is missing
SELECT SUM(`Subscription_Services`.`Price`) FROM `Subscription_Services`
LEFT JOIN `Subscription_Services` ON `Subscription_Services`.`Subscription_id`=`Subscriptions`.`id`
WHERE `Subscriptions`.`id`='%ID%'

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated field from 3rd table

Post by pbottcher » 2021-05-31 19:53

Hi,

you may try

Code: Select all

SELECT SUM(`Services`.`Price`) FROM `Services` left join `Subscription_Services`
on `Subscription_Services`.`Service`=`Services`.`id` 
LEFT JOIN `Subscriptions` ON `Subscription_Services`.`Subscription_id`=`Subscriptions`.`id`
WHERE `Subscriptions`.`id`='%ID%'
Code is not tested, so look for syntax errors.
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.

gatenet
Veteran Member
Posts: 45
Joined: 2016-07-26 09:34
Location: Greece
Contact:

Re: Calculated field from 3rd table

Post by gatenet » 2021-06-01 08:04

Thank you very much my friend, it worked like a charm, one more question, how can we format this field with the € sign? Thank you again

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Calculated field from 3rd table

Post by pbottcher » 2021-06-01 21:11

Hi,

just use

Code: Select all

concat (SUM(`Services`.`Price`),' €')
or

Code: Select all

concat ('€ ',SUM(`Services`.`Price`))
but make sure your fielddefinition can hold that
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.

gatenet
Veteran Member
Posts: 45
Joined: 2016-07-26 09:34
Location: Greece
Contact:

Re: Calculated field from 3rd table

Post by gatenet » 2021-06-02 15:24

You are the best

Post Reply