[SOLVED] count totals in array results

[giraffemedia]

Hi

 

i'm not sure about the best way to go about counting total prices for each customer that i've returned from query. I've used array_sum to calculate the total amount from ALL results returned, but within the results are multiple rows for the same customers - they are separate orders.

 

I would like to get a total of all orders for each customer, but i'm having trouble trying to group all results by a certain id or company name.

 

Here is the result I am getting at the moment. The first column is the booking form number, the next is the issue the customer is booked into, the third is the company name and finally the price for that booking.

 

1284 - 61 - Test - £528.75
1264 - 61 - Testing - £575.75
1264 - 62 - Testing - £575.75
1284 - 62 - Test - £528.75
1262 - 62 - Testing - £264.38
1262 - 63 - Testing - £264.38
1264 - 63 - Testing - £575.75
1284 - 63 - Test - £528.75

£3842.26 

 

I would like it to read something like

 

1284 - Test - £1586.25
1264 - Testing - £1727.25
1262 - Testing - £528.76

£3842.26 

 

Anyone have any ideas?

 

Thanks

 

James

[valtido]

is this arrey comming from a database right?

 

if so then you have gone the wrong way of calculating it lol

 

 

[giraffemedia]

is this arrey comming from a database right?

 

if so then you have gone the wrong way of calculating it lol

 

 

 

What sort of reply is that?

[JasonLewis]

I'm not sure how you're grouping, but something like this maybe:

 

$query = "SELECT SUM(`price`) as price, id, company FROM tableName GROUP BY id, company";
$run = mysql_query($query) or die(mysql_error());

while($r = mysql_fetch_assoc($run)){
echo "{$r['id']} - {$r['company']} - {$r['price']}<br>";
}

[giraffemedia]

I've tried using GROUP BY in my query but it's only returning 1 result for each customer, not multiples.

 

here is my test query...

 

$forms_query = ("SELECT * FROM issues_booked AS i JOIN booking_form AS b ON i.ib_booking_form_number = b.bf_id JOIN contacts AS c ON c.contacts_id = b.bf_company_id WHERE i.ib_issue_number IN ($issues) AND c.client_lead = 'C' GROUP BY b.bf_id ORDER BY i.ib_issue_number ASC") or die(0);

$forms_result = mysql_query ($forms_query);

if (!$forms_result) { 
echo ("Problem because: " . mysql_error());
}

while ($forms_row = mysql_fetch_array($forms_result)) {

$test[] = $forms_row['ib_booking_form_number'] . ' - ' . $forms_row['ib_issue_number'] . ' - ' . $forms_row['companyname'] . ' - £' . $forms_row['bf_issue_cost'];

$testing[] = number_format($forms_row['bf_issue_cost'], 2, '.', '');
}

echo implode('<br /> ', $test);


echo '<br /><br />' . '£' . array_sum($testing);

 

Does the order of joining the tables in mysql affect this?

[giraffemedia]

Anyone?

[giraffemedia]

Managed to sort it by using SUM() in my mysql query. Didn't even know it existed!

 

James