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%'
Calculated field from 3rd table
Re: Calculated field from 3rd table
Hi,
you may try
Code is not tested, so look for syntax errors.
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%'
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: Calculated field from 3rd table
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
Re: Calculated field from 3rd table
Hi,
just use or
but make sure your fielddefinition can hold that
just use
Code: Select all
concat (SUM(`Services`.`Price`),' €')
Code: Select all
concat ('€ ',SUM(`Services`.`Price`))
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: Calculated field from 3rd table
You are the best