CODEWITHSUNDEEP
mysqlsqlgroup-concat
Jordan
Jordan

Reputation: 157

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   | 
+---------+----------+

Upvotes: 1

Views: 426

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32402

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

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You have to remove Day in the GROUP BY clause. Additionally, replace *, with Product:

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

ONLINE DEMO

Upvotes: 1

Related Questions