View Full Version : Sum Of Filtered Results - LARGE

12-17-2010, 09:49 PM
I've searched the forums, and found a topic close to this one, but I was unable to resolve my issue.

Here's the problem.

I'm a web developer, but I'm not that proficient with MySql. My company developed a database solution for a local company which has grown from a basic "Website" with records to more of a "program" and the original MySql/PHP developer has left, leaving me to troubleshoot and add new features.

The company wants to run reports on warranties filed by their dealers.
The reports can be filtered via 17 different types of options (Product Type, state, and so forth)

The Results contain dollar amounts for Shipping, parts, labor, and item price.

I can easily see the amounts per each row.

however They have requested to have the totals of each column at the bottom.

I have searched and read and searched again, and I have no clue how to do this, or even the best way to go about it. Please help!.

Here's the core query:

$maxRows_WADAwarranty_claims_full = 1000;
$pageNum_WADAwarranty_claims_full = 0;
if (isset($_GET['pageNum_WADAwarranty_claims_full'])) {
$pageNum_WADAwarranty_claims_full = $_GET['pageNum_WADAwarranty_claims_full'];
$startRow_WADAwarranty_claims_full = $pageNum_WADAwarranty_claims_full * $maxRows_WADAwarranty_claims_full;

mysql_select_db($database_CSCd, $CSCd);
$query_WADAwarranty_claims_full = "SELECT dealer, cus_fname, cus_lname, cus_address, cus_city, cus_state, product_type, product_class, motorcycle_make_model, motorcycle_model_name, csc_vin, dot_vin, orderNum, claim_number, claim_description, claim_status, claim_date, shipping_cost, purchase_date, sku, warranty_labor_code, labor_units, labor_rate, item_quantity, item_price, item_cost, extended_item_price, extended_item_cost, extended_labor_cost FROM warranty_claims_full";
$query_limit_WADAwarranty_claims_full = sprintf("%s LIMIT %d, %d", $query_WADAwarranty_claims_full, $startRow_WADAwarranty_claims_full, $maxRows_WADAwarranty_claims_full);
$WADAwarranty_claims_full = mysql_query($query_limit_WADAwarranty_claims_full, $CSCd) or die(mysql_error());
$row_WADAwarranty_claims_full = mysql_fetch_assoc($WADAwarranty_claims_full);

if (isset($_GET['totalRows_WADAwarranty_claims_full'])) {
$totalRows_WADAwarranty_claims_full = $_GET['totalRows_WADAwarranty_claims_full'];
} else {
$all_WADAwarranty_claims_full = mysql_query($query_WADAwarranty_claims_full);
$totalRows_WADAwarranty_claims_full = mysql_num_rows($all_WADAwarranty_claims_full);
$totalPages_WADAwarranty_claims_full = ceil($totalRows_WADAwarranty_claims_full/$maxRows_WADAwarranty_claims_full)-1;

I need the extended_item_price, extended_item_cost, extended_labor_cost, and shipping_cost totalled at the bottom.

Hopefully this is enough info.

Thanks again to anyone willing to help.

12-29-2010, 09:36 PM
$sqlres = mysql_query($query_WADAwarranty_claims_full);
while ($row = mysql_fetch_assoc($sqlres)) {
$totalLC += $row['extended_labor_cost'];
$totalSC += $row['shipping_cost'];
$totalEIC += $row['extended_item_cost'];
$totalEPP += $row['extended_item_price'];

This fixed it.

Just in case anyone else was wondering.