I need to select the customer's name along with the phone number.
I have tried INNER JOIN with DISTINCT SELECT, but the problem is, that a customer enters a different name each time. Thus, DISTINCT can't solve it correctly.
and selecting name would give the following error as it is not in the group by list.
#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'courier.temp.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
How to reproduce
Database
CREATE TABLE `temp` (
`id` bigint UNSIGNED NOT NULL,
`name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`phone` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`status` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO temp (id, name, phone, status) VALUES
(1, 'Chadd Bode', '364.424.6446', 'Delivered'),
(2, 'Ewell Gaylord', '+1-551-910-8019', 'Cancelled'),
(3, 'Madison Wiegand', '+1-551-910-8019', 'Cancelled'),
(4, 'Prof. Brannon Lubowitz', '+1-551-910-8019', 'Returned'),
(5, 'Holden Strosin Sr.', '+1-551-910-8019', 'Returned'),
(6, 'Crystel Aufderhar', '+1-551-910-8019', 'Delivered'),
(7, 'Dr. Matt Jacobs', '+1-551-910-8019', 'Cancelled'),
(8, 'Prof. Jedidiah Schinner Sr.', '+1-551-910-8019', 'Returned'),
(9, 'Mr. Griffin Herman MD', '+1-551-910-8019', 'Returned'),
(10, 'Sonya Legros', '+1-551-910-8019', 'Returned'),
(11, 'Rowena Waelchi', '+1-551-910-8019', 'Returned'),
(12, 'Aracely Reinger', '+15513763021', 'Returned'),
(13, 'Isom Collier', '+15513763021', 'Returned'),
(14, 'Darrel Wolf', '+1.262.771.7486', 'Delivered'),
(15, 'Marina Mosciski', '+1.262.771.7486', 'Cancelled'),
(16, 'Ivah Haag Jr.', '+1.262.771.7486', 'Cancelled'),
(17, 'Christine Gibson Jr.', '(678) 209-8983', 'Delivered'),
(18, 'Christop Yundt', '(678) 209-8983', 'Cancelled'),
(19, 'Dr. Darion Maggio Jr.', '(682) 950-4559', 'Placed'),
(20, 'Jailyn Hodkiewicz', '(682) 950-4559', 'Returned'),
(21, 'Mr. Ellsworth Frami', '(475) 252-5533', 'Returned'),
(22, 'Lyla Bogisich I', '(475) 252-5533', 'Cancelled'),
(23, 'Erwin Wisoky', '(475) 252-5533', 'Cancelled'),
(24, 'Dr. Broderick Paucek I', '(475) 252-5533', 'Delivered'),
(25, 'Miss Jacinthe Schowalter IV', '(475) 252-5533', 'Delivered'),
(26, 'Ida Rutherford', '(475) 252-5533', 'Cancelled'),
(27, 'Prof. Ruthie Bartoletti III', '(475) 252-5533', 'Cancelled'),
(28, 'Ali Volkman', '(475) 252-5533', 'Cancelled'),
(29, 'Ms. Kathryne Schneider DDS', '(475) 252-5533', 'Cancelled'),
(30, 'Blake Zieme', '(475) 252-5533', 'Delivered'),
(31, 'Mr. Jaron McCullough', '407-739-1106', 'Returned'),
(32, 'Forrest Mueller', '407-739-1106', 'Delivered'),
(33, 'Bobbie Russel', '240-726-6031', 'Returned'),
(34, 'Maia Doyle', '240-726-6031', 'Placed'),
(35, 'Price Reichel', '+1-805-512-9773', 'Delivered'),
(36, 'Dr. Reynold Smitham', '+1-805-512-9773', 'Delivered'),
(37, 'Ms. Arlene White PhD', '+1-805-512-9773', 'Placed'),
(38, 'Quinton Cummings', '+1-805-512-9773', 'Cancelled'),
(39, 'Bridgette Harvey', '+1-805-512-9773', 'Returned'),
(40, 'Erica Brakus MD', '+1-805-512-9773', 'Cancelled'),
(41, 'Salvador Bednar', '+1-805-512-9773', 'Cancelled');
My Attempt
SELECT
recipient_table.phone AS phone,
/*recipient_table.name AS name,*/
COALESCE(total_placed_table.placed_orders, 0) AS placed_orders,
COALESCE(total_delivered_table.delivered_orders, 0) AS delivered_orders,
COALESCE(total_returned_table.returned_orders, 0) AS returned_orders,
COALESCE(total_cancelled_table.cancelled_orders, 0) AS cancelled_orders
FROM
(
SELECT temp.phone/*, temp.name*/
FROM temp
WHERE temp.status IN ("Placed", "Delivered", "Returned", "Cancelled")
/* USING WHERE BECAUSE THERE CAN BE EVEN MORE STATUS */
GROUP BY temp.phone
) AS recipient_table
LEFT JOIN (
SELECT temp.phone, COUNT(temp.id) AS placed_orders
FROM temp
WHERE temp.status IN ("Placed")
GROUP BY temp.phone
) AS total_placed_table ON total_placed_table.phone = recipient_table.phone
LEFT JOIN (
SELECT temp.phone, COUNT(temp.id) AS delivered_orders
FROM temp
WHERE temp.status IN ("Delivered")
GROUP BY temp.phone
) AS total_delivered_table ON total_delivered_table.phone = recipient_table.phone
LEFT JOIN (
SELECT temp.phone, COUNT(temp.id) AS returned_orders
FROM temp
WHERE temp.status IN ("Returned")
GROUP BY temp.phone
) AS total_returned_table ON total_returned_table.phone = recipient_table.phone
LEFT JOIN (
SELECT temp.phone, COUNT(temp.id) AS cancelled_orders
FROM temp
WHERE temp.status IN ("Cancelled")
GROUP BY temp.phone
) AS total_cancelled_table ON total_cancelled_table.phone = recipient_table.phone;
Can anyone suggest me a solution?
