Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > CREATE TABLE nextgenorders2 > (`nextgenorder_companyname` varchar(9), `nextgenorder_company_entity` int, `nextgenorder_ordernumber` int, nextgenorder_deliverydate date) > ; > > INSERT INTO nextgenorders2 > (`nextgenorder_companyname`, `nextgenorder_company_entity`, `nextgenorder_ordernumber`,nextgenorder_deliverydate) > VALUES > ('Apple', 123, 555123,'2020-11-11'), > ('Apple', 123, 555123,'2020-11-11'), > ('Telsa', 888, 444444,'2020-11-11'), > ('Telsa', 777, 222222,'2020-11-11'), > ('Apple NYC', 103, 544523,'2020-11-11'), > ('Stack', 222, 511523,'2020-11-11'), > ('Stack', 222, 144523,'2020-11-11') > ; > > <pre> > ✓ > > ✓ > </pre> <!-- --> > SELECT > GROUP_CONCAT(n2.nextgenorder_ordernumber), > #n2.nextgenorder_serial, > MIN(n2.nextgenorder_company_entity), > n2.nextgenorder_companyname, > n2.nextgenorder_deliverydate > FROM > (SELECT REPLACE(`nextgenorder_companyname`,'Apple NYC','Apple') `nextgenorder_companyname` > , `nextgenorder_company_entity`, `nextgenorder_ordernumber`,nextgenorder_deliverydate > FROM nextgenorders2) n2 > WHERE n2.nextgenorder_deliverydate='2020-11-11' > GROUP BY n2.nextgenorder_companyname > ,n2.nextgenorder_deliverydate > #,n2.nextgenorder_ordernumber > > <pre> > GROUP_CONCAT(n2.nextgenorder_ordernumber) | MIN(n2.nextgenorder_company_entity) | nextgenorder_companyname | nextgenorder_deliverydate > :---------------------------------------- | ----------------------------------: | :----------------------- | :------------------------ > 555123,555123,544523 | 103 | Apple | 2020-11-11 > 511523,144523 | 222 | Stack | 2020-11-11 > 444444,222222 | 777 | Telsa | 2020-11-11 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2c03df6e0af433c556c42094fff5e125)*
back to fiddle