Getting Sum on multiple fields in one Select

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Getting Sum on multiple fields in one Select

Post by marnieg »

I have several columns that I want the sum of based on a criteria. I want to place these individual sums into variables. Is it possible to do this in one Select statement. Here is a portion of my code.

Code: Select all

$stquery = "Select sum(ts_calc_st) from timesheets  where ts_insp_id = $inspid and ts_wo_num = '$tswo' and ts_status = 'A' and ts_date >= '$fmdate' and ts_date <= '$todate'";
$strow = mysql_query($stquery);
$sttot = mysql_result($strow,0);
$otquery = "Select sum(ts_calc_ot) from timesheets  where ts_insp_id = $inspid and ts_wo_num = '$tswo' and ts_status = 'A' and ts_date >= '$fmdate' and ts_date <= '$todate'";
$otrow = mysql_query($otquery);
$ottot = mysql_result($otrow,0);
Notice the select statement is the same, but I need the sum of each of these fields in a variable. I have more columns to get sum on, but don't want to have so many sql statements if not necessary.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Getting Sum on multiple fields in one Select

Post by AbraCadaver »

Something like:

Code: Select all

$stquery = "SELECT sum(ts_calc_st) AS st_sum, sum(ts_calc_ot) AS ot_sum FROM timesheets
WHERE ts_insp_id = $inspid AND ts_wo_num = '$tswo' AND ts_status = 'A' AND ts_date >= '$fmdate' AND ts_date <= '$todate'";
$result = mysql_query($stquery);
$row = mysql_fetch_assoc($result);
//then you can use $row['st_sum'] and $row['ot_sum']
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
marnieg
Forum Commoner
Posts: 65
Joined: Wed Mar 12, 2003 4:35 pm

Re: Getting Sum on multiple fields in one Select

Post by marnieg »

Thank you! I knew I could do multiple sums in the select but wasn't sure how to address them as individual variables.

This forum always comes through for me and in a timely manner. :D
Post Reply