Page 1 of 1

Getting Sum on multiple fields in one Select

Posted: Wed Jan 05, 2011 2:48 pm
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.

Re: Getting Sum on multiple fields in one Select

Posted: Wed Jan 05, 2011 2:54 pm
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']

Re: Getting Sum on multiple fields in one Select

Posted: Wed Jan 05, 2011 3:15 pm
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