Search code examples
mysqlsqlgroup-concat

MySQL Group_Concat two tables together

I'm trying to group my data in my tables by all the prices that I have recorded over x days. I'm having trouble with the SQL statement, here is my current statement

SELECT *, GROUP_CONCAT(Price) AS Prices FROM product_prices GROUP BY Product,Day

Here is my product_prices table;

+---------+--------------+
| Product | Day | Price  |
+---------+--------------+
|    Phone| 1   |  100   |
|  Speaker| 1   |  50    |
|   Phone | 2   |  230   |
|  Speaker| 2   |  80    |
+---------+--------------+

Where I want the prices to be group in 1 table by the day like this

+---------+----------+
| Product | Prices   |
+---------+----------+
|    Phone| 100, 230 |
|  Speaker| 50, 80   | 
+---------+----------+
Solution

  • If you want to select the prices from lowest to highest, you should use order by in your group_concat statement:

    SELECT Product, 
GROUP_CONCAT(Price ORDER BY Price) AS Prices 
FROM product_prices 
GROUP BY Product

    SQLFiddle