2. PHPRunner General questions
  3. Sum Calculation Between 2 Tables
This topic is locked

Sum Calculation Between 2 Tables

 		12/1/2013 7:10:49 AM
PHPRunner General questions
S
 sfhussain author

Hi,
I have 2 Tables:

  1. Items ---> Fields are : unit_price, quantity, total_price
  2. Discounts----> Field are: discount, grand_total
    How can I calculate the sum of all records from column grand_total field.
    Furthermore can we do it on the fly as both tables are showing on the same Add page of Master Table.
    Can anybody help please.
    Thanks.
C
 		cgphp 12/1/2013

What is the link field between the two tables?
S
 		sfhussain author 12/1/2013



What is the link field between the two tables?


Till now i didn't make any link, But i can make link between total_price and grand_total. You can suggest me also which can be the best.
Sergey Kornilov admin 12/2/2013

There must some sort of link between these two tables so you can retrieve some data from the second table.
If your goal is to calculate the discount based on the total purchase amount link is not required and here is your sample code.

select discount from Discounts where grand_total > {total_price} order by grand_total desc limit 0,1


Instead of {total_price} use the calculated total_price value. I guess you have it somewhere in your code. This SQL query will return you the highest discount available based on total_price value.
S
 		sfhussain author 12/3/2013



There must some sort of link between these two tables so you can retrieve some data from the second table.
If your goal is to calculate the discount based on the total purchase amount link is not required and here is your sample code.

select discount from Discounts where grand_total > {total_price} order by grand_total desc limit 0,1


Instead of {total_price} use the calculated total_price value. I guess you have it somewhere in your code. This SQL query will return you the highest discount available based on total_price value.


Thanks brother for the help, But i don't want discount value yet. First I need grand_total value because there might be more then 1 row for total_price values so i will sum all of them and then make grand_total, Discount is based on grand_total.
Sergey Kornilov admin 12/3/2013

Calculate your grand total first. Then issue a suggested SQL query to pull the discount based on the total you just calculated.
S
 		sfhussain author 12/3/2013



Calculate your grand total first. Then issue a suggested SQL query to pull the discount based on the total you just calculated.


Hi,
I Exactly need the option given in Invoice Template of PHPR. Please check the link for reference. How can we calculate the SUBTOTAL field on the fly in invoice template.
Invoice Template
Please help.
Sergey Kornilov admin 12/3/2013

If Invoice template is exactly what you looking for I would suggest to get that template and learn how things work there.