ster ster - 3 months ago 11
MySQL Question

MySQL - PHP unique values from rows

I have this table:
table 1



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





and this code sql:



SELECT
COUNTRY,
SUM(COALESCE(IF(QUANTITY = NULL OR QUANTITY = 0,1,QUANTITY), 1) * EACH) AS PRICE
FROM table1
GROUP BY COUNTRY





How could I make unique values for the country column and return: USA = 48 (ID: 1+5+7); UK= 6; GERMANY=3; FRANCE = 44 (ID: 4+6+8). I want that the rows, those contain two, three, four countries to be eliminated and to remain only the first country from row.
Thank you!

Answer

Use substring_index to get the first country in the ;-separated list.

SELECT
  SUBSTRING_INDEX(COUNTRY, ';', 1) AS COUNTRY
  SUM(IF(QUANTITY = NULL OR QUANTITY = 0,1,QUANTITY) * EACH) AS PRICE
FROM table1
GROUP BY COUNTRY

It would be much more complicated if you wanted to keep all the rows where each country appears (in that case I would recommend doing it in PHP, not MySQL, since MySQL doesn't have a builtin way to do explode()).