PHP - Concatenate rows from MySQL

Question

My table is, I know that the table terrible but is old: http://sqlfiddle.com/#!9/f2680/1

+----+-----------------------+----------+------+
| ID | COUNTRY               | QUANTITY | EACH |
+----+-----------------------+----------+------+
| 1  | U.S.A                 |     1    |  12  |
| 2  | U.K.                  |     2    |   3  |
| 3  | GERMANY               |   NULL   |   3  |
| 4  | FRANCE;GERMANY; U.S.A |     0    |   7  |
| 5  | U.S.A;GERMANY         |     3    |   8  |
| 6  | GERMANY;FRANCE;U.S.A  |     1    |  10  |
+----+-----------------------+----------+------+

<?php
include 'bd_cnx.php';
  $ret =[['Test','Valori livrare']];
  $sql = "SELECT SUBSTRING_INDEX(COUNTRY, ';', 1) AS COUNTRY,
                 SUM(COALESCE(IF(QUANTITY = NULL OR QUANTITY = 0,1,QUANTITY),1) * EACHCOL) AS PRICE
          FROM table1
          GROUP BY COUNTRY";

  $result = $conn->query($sql);
  if ($result->num_rows > 0){
    while($row = $result->fetch_assoc()){
      $ret[] =[$row['COUNTRY'], floatval($row['PRICE'])];
    }
 }
  echo json_encode($ret);
?>

My SQL code returns rows: FRANCE=3, GERMANY=6, GERMANY=12, U.S.A=12, U.S.A=7

I want to group the resulted values from the COUNTRY column (to make them unique) and then make SUM and finaly to result the next form: FRANCE=3; GERMANY=18; U.S.A=19. I will introduce these rows in google chart in the next form: ['country','value']. Thank you!

Answer 1

Use this query with additional select ... group by on the first query:

select COUNTRY, sum(PRICE) from(
SELECT
  SUBSTRING_INDEX(COUNTRY, ';', 1) AS COUNTRY,
  SUM(COALESCE(IF(QUANTITY = NULL OR QUANTITY = 0,1,QUANTITY),1) * EACHCOL) AS PRICE
FROM table1
GROUP BY COUNTRY) as t
group by COUNTRY;