2. PHPRunner General questions
  3. Simple Sum Query
This topic is locked

Simple Sum Query

 		10/22/2008 3:24:39 PM
PHPRunner General questions
bbarker author

Sorry for posting this, but I've gotten wrapped up in building "running balances", when I only wanted a "sum".
I understand how to place this in the properties of the field that I want to fill in. What I can't figure out is how to sum detail amounts (eg. Fee) only for the current order (eg. person).
global $data,$conn;

SELECT sum(Fee) FROM `meals` WHERE `IDmember` =".$data[IDmember]";

$value = $data[0];
Table = meals

Records = Fee, ID, IDmember, Description
All meals records each have an "ID"

All members each have a "IDmember"
How do I sum up all meal fees for a single member?
Sergey Kornilov admin 10/22/2008
SELECT IDmember , sum(Fee) FROM `meals`

GROUP By IDmember


More info:

http://www.webcheatsheet.com/sql/interacti...sql_groupby.php
bbarker author 10/24/2008

[quote name='admin' date='Oct 22 2008, 02:30 PM' post='34257']

SELECT IDmember , sum(Fee) FROM `meals`

GROUP By IDmember


Thanks. The summing works, but I was trying to only sum for a single person and store it so that I can print out a report for a single person on-demand.
I think, instead of GROUP, I need a WHERE clause. If my Master table (members) used ID as it's key, and the child table (meals) uses IDmember to link to it, how do I select only the SUM for that member?
One other question on tactic. Where should I store sums like this? In a hidden field on the Master table? or a new table which includes a whole bunch of sums?
bbarker author 10/27/2008

Select Sum(Fee)

From meals

Where IDmember = $IDmember_value

Group by IDmember
I'm still not able to Sum -AND- restrict the display it to a single user.
IDmember not a global variable. It is actually the foreign key for the meals table.
I want to post this in the Custom area of Properties for the TOTAL FEE field.
J
 		Jane 10/28/2008

Bill,
if you set up master-detail relationships where clause should be added to the SQL query automatically.

If you want to save sum value in the database calculate sum and save it in the field in the Master table in the After record added/update events on the Events tab.
bbarker author 10/29/2008

Bill,

if you set up master-detail relationships where clause should be added to the SQL query automatically.

If you want to save sum value in the database calculate sum and save it in the field in the Master table in the After record added/update events on the Events tab.


Thanks Jane... but I'm just not making progress with it. I spent another 6 hours tonight and still can't get the variables figured out.
I uploaded the application to the DEMO ACCOUNT and sent an email to "support" with ID and PW.
Sergey Kornilov admin 10/30/2008

What's your email address?
bbarker author 10/30/2008

What's your email address?


bill@barkerville.net