By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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')
;
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
| 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 |