DB query foreach rows that are equal calculate the values from other column

I have a db table with in one column the province name of a country. And in another column of the same table the amount of people living in this province.

There are many more columns in the table and also postal codes. As the postal codes are unique you can see in this table that the province column has a lot of equals.

Sample data:

id | postal code | province  | amount
 1 | 1001        | Amsterdam |    500
 2 | 1002        | Amsterdam |   1500
 3 | 1003        | Amsterdam |    250

In a form that I am creating I need to create checkboxes for each province and the value of the checkbox needs to be the amount value.

For this I use the below code. (This code is inserted into a Form Builder called RS Form so it might look a bit strange but please believe me it is working.

The form builder creates the checkboxes it only needs the ‘grab from db’ part not the echo checkboxes part).

<?php

//<code>
$db =& JFactory::getDBO();
$db->setQuery("SELECT province, amount FROM `#_the_correct_table`");
$results = $db->loadObjectList();

  foreach ($results as $result) {
    $value = $result->amount;
    $label = $result->province;
    $items[] = $value.'|'.$label;
  }

  // Multiple values are separated by new lines, so we need to do this now
  $items = implode("n", $items);

  // Now we need to return the value to the field
  return $items;
//</code>

?>

The above code is currently generating about 4000+ checkboxes with lots of duplicates, so I have been looking into outputting each province only once and making it calculate the amounts. So with the above example I would get 1 checkbox Amsterdam = 2250.

I am struggling with getting this code which should do this combining to work with the code I already have.

$sum = 0;
foreach($quantity as $value) $sum = $sum + $value;
echo $sum;

Can someone please show me how to combine it?

Answer

No need to do it using PHP. This is a standard SQL task.

You need to perform 2 operations:

group your records by province name (Amsterdam for example)
sum amount values for grouped rows

Just change your SQL query to something like:

SELECT SUM(amount) as province_sum
FROM tbl
GROUP BY province

Here is the SQL fiddle: http://sqlfiddle.com/#!9/9cbf9d/1

Advertisement

Answer