How to combine row based on column's value that is distinct in MySQL

Question

This is my first post here.

My question is, how to combine row when timestamp (base on date only Y-m-d), item_condition and equipment_spare_id are same. Then GROUP_CONCAT remark and serial_number.

In this case, it should has 2 row.

Thanks in advance.

Please edit your question and post the expected result as table. — Paul Spiegel
– Paul Spiegel, Commented Sep 14, 2019 at 14:10

Gordon Linoff · Accepted Answer · 2019-09-14 14:03:27Z

1

You seem to want group by. You are not clear on what the result set should look like, but the idea is:

select date(timestamp) as dte, item_condition, equipment_spare_id,
       count(*)
from t
group by dte, item_condition, equipment_spare_id;

answered Sep 14, 2019 at 14:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

GMB · Accepted Answer · 2019-09-14 14:06:10Z

1

You can use GROUP BY and GROUP_CONCAT as follows:

SELECT 
    date(timestamp) as dte, 
    item_condition, 
    equipment_spare_id,
    GROUP_CONCAT(remark) all_remarks,
    GROUP_CONCAT(serial_number) all_serials
FROM mytable
GROUP BY 
    date(timestamp), 
    item_condition, 
    equipment_spare_id

answered Sep 14, 2019 at 14:06

GMB

224k25 gold badges103 silver badges151 bronze badges

Collectives™ on Stack Overflow

How to combine row based on column's value that is distinct in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related