MySQL - PHP unique values from rows

Question

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!

That's a terrible table. You should have one country per COUNTRY row. Then you relate different countries through a common mapping. The only way you can do this is to query the table, then compare the strings. Which ignores everything about the database design. — Aaron
– Aaron, Commented Aug 29, 2016 at 18:06
Wait...do you want to eliminate records with more than one country, or do you want to retain them but keep only the first country? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 29, 2016 at 18:08
You would fix the data model to have a table with one row per id and country. That is the SQLish way to represent a list -- not a delimited separated list. — Gordon Linoff
– Gordon Linoff, Commented Aug 29, 2016 at 18:10
That being said, if you need CSV presentation, you could always aggregate using GROUP_CONCAT. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 29, 2016 at 18:12
BTW, there's no need for COALESCE. The IF prevents any null values from being produced. — Barmar
– Barmar, Commented Aug 29, 2016 at 18:33

Barmar · Accepted Answer · 2016-08-29 21:36:24Z

4

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

SELECT
  SUBSTRING_INDEX(COUNTRY, ';', 1) AS COUNTRY
  SUM(IF(QUANTITY IS 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()).

edited Aug 29, 2016 at 21:36

answered Aug 29, 2016 at 18:37

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Spiegel Over a year ago

QUANTITY = NULL should be changed to QUANTITY IS NULL. That is why OP used COALESCE.

Collectives™ on Stack Overflow

MySQL - PHP unique values from rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related