How to apply GROUP BY to multiple SELECT statements in MySQL
I creating multipal select query that selects data from multiple tables. I have completed all the query but now I have to apply the GROUP BY to the SECOND select query but getting error is Subquery returns more than 1 row.
GROUP BY in not working on suquery getting error is Subquery returns more than 1 row.
I am using the below query:
SELECT ( SELECT COUNT(bookings.user_id) as cleanings FROM users INNER JOIN bookings ON bookings.user_id = users.id WHERE users.is_provider = 1 AND bookings.booking_status_id = 4 GROUP BY user.id) AS cleanings, users.first_name, users.last_name, providerservicemaps.amount, TRUNCATE(SUM(userreviews.rating)*5/(COUNT(userreviews.user_review_by)*5), 1) AS rating, users.profilepic, postcodes.postcode, postcodes.suburb, servicecategories.name, provider_working_hours.start_time, provider_working_hours.end_time FROM users INNER JOIN provider_working_hours ON provider_working_hours.provider_id = users.id INNER JOIN provider_postcode_maps ON provider_postcode_maps.provider_id = users.id INNER JOIN postcodes ON postcodes.id = provider_postcode_maps.postcode_id INNER JOIN providerservicemaps ON providerservicemaps.provider_user_id = users.id INNER JOIN userreviews ON userreviews.user_review_for = users.id INNER JOIN services ON providerservicemaps.service_id = services.id INNER JOIN servicecategories ON services.category_id = servicecategories.id INNER JOIN bookings ON bookings.user_id = users.id INNER JOIN bookingstatuses ON bookings.booking_status_id = bookingstatuses.id where users.is_provider=1 AND postcodes.postcode LIKE '$postcode' AND postcodes.suburb LIKE '$suburb' AND servicecategories.name LIKE '$catagories' AND FIND_IN_SET('$working_day',provider_working_hours.working_days) AND provider_working_hours.start_time>='$start_time_result'AND provider_working_hours.end_time>='$end_time_result' AND bookings.booking_time>='$start_time_result' AND bookings.number_of_hours>='$work_hours' GROUP BY users.first_name, users.last_name, providerservicemaps.amount, userreviews.user_review_for, users.profilepic, postcodes.postcode, postcodes.suburb, servicecategories.name, provider_working_hours.start_time, provider_working_hours.end_time; 
This is your source of error:
( SELECT COUNT(bookings.user_id) as cleanings FROM users INNER JOIN bookings ON bookings.user_id = users.id WHERE users.is_provider = 1 AND bookings.booking_status_id = 4 GROUP BY user.id) AS cleanings If you want to return someting as a column it needs to be an atomic value. So you cannot return multiple rows.
Not having any context, I can just give you a way to get rid of the error by using a CTE. You will have to decide if the logic is preserved.
WITH CTE AS ( SELECT users.user_id, COUNT(bookings.user_id) as cleanings FROM users INNER JOIN bookings ON bookings.user_id = users.id WHERE users.is_provider = 1 AND bookings.booking_status_id = 4 GROUP BY user.id ) SELECT CTE.cleanings, users.first_name, users.last_name, providerservicemaps.amount, TRUNCATE(SUM(userreviews.rating)*5/(COUNT(userreviews.user_review_by)*5), 1) AS rating, users.profilepic, postcodes.postcode, postcodes.suburb, servicecategories.name, provider_working_hours.start_time, provider_working_hours.end_time FROM users INNER JOIN CTE ON CTE.user_id=users.user_id INNER JOIN provider_working_hours ON provider_working_hours.provider_id = users.id INNER JOIN provider_postcode_maps ON provider_postcode_maps.provider_id = users.id INNER JOIN postcodes ON postcodes.id = provider_postcode_maps.postcode_id INNER JOIN providerservicemaps ON providerservicemaps.provider_user_id = users.id INNER JOIN userreviews ON userreviews.user_review_for = users.id INNER JOIN services ON providerservicemaps.service_id = services.id INNER JOIN servicecategories ON services.category_id = servicecategories.id INNER JOIN bookings ON bookings.user_id = users.id INNER JOIN bookingstatuses ON bookings.booking_status_id = bookingstatuses.id where users.is_provider=1 AND postcodes.postcode LIKE '$postcode' AND postcodes.suburb LIKE '$suburb' AND servicecategories.name LIKE '$catagories' AND FIND_IN_SET('$working_day',provider_working_hours.working_days) AND provider_working_hours.start_time>='$start_time_result'AND provider_working_hours.end_time>='$end_time_result' AND bookings.booking_time>='$start_time_result' AND bookings.number_of_hours>='$work_hours' GROUP BY users.first_name, users.last_name, providerservicemaps.amount, userreviews.user_review_for, users.profilepic, postcodes.postcode, postcodes.suburb, servicecategories.name, provider_working_hours.start_time, provider_working_hours.end_time; An alternative would be to use a correlated subquery for the cleanings column like so:
( SELECT COUNT(bookings.user_id) as cleanings FROM bookings WHERE user_id = users.id WHERE booking_status_id = 4 ) AS cleanings 
