Why integer cast is not working with integer group_concat() list?

I’m stuck at the query where I need to concat IDs of the table. And from that group of IDs, I need to fetch that rows in sub query. But when I try to do so, MySQL consider group_concat() as a string. So that condition becomes false.

select count(*) 
from rides r 
where r.ride_status = 'cancelled' 
      and r.id IN (group_concat(rides.id))

*************** Original Query Below **************

-- Daily Earnings for 7 days [Final]

select 
          group_concat(rides.id) as ids, 
          group_concat(ride_category.name) as rideType, 
          group_concat(ride_cars.amount + ride_cars.commission) as rideAmount ,
          group_concat(ride_types.name) as carType,
          count(*) as numberOfRides,
          (
            select count(*) from rides r where r.ride_status = 'cancelled' and r.id IN  (group_concat(rides.id) )
          ) as cancelledRides,
          (
            select count(*) from rides r where r.`ride_status` = 'completed' and r.id IN (group_concat(rides.id))
          ) as completedRides,
          group_concat(ride_cars.status) as status,
          sum(ride_cars.commission) + sum(ride_cars.amount) as amount, 
          date_format(from_unixtime(rides.requested_at/1000 + rides.offset*60), '%Y-%m-%d') as requestedDate, 
          date_format(from_unixtime(rides.requested_at/1000 + rides.offset*60), '%V') as week
from 
          ride_cars,
          rides,
          ride_category,
          ride_type_cars,
          ride_types
where 
          ride_cars.user_id = 166
          AND (rides.ride_status = 'completed' or.  rides.ride_status = 'cancelled')
          AND ride_cars.ride_id = rides.id 
          AND (rides.requested_at >= 1559347200000 AND requested_at < 1561852800000)
          AND rides.ride_category = ride_category.id 
          AND ride_cars.car_model_id = ride_type_cars.car_model_id
          AND ride_cars.ride_type_id = ride_types.id
group by
          requestedDate;

Any solutions will be appreciated.

Answer

Try to replace the sub-query

(select count(*) from rides r where r.ride_status = 'cancelled' and r.id IN  (group_concat(rides.id) )) as cancelledRides,

with below to count using SUM and CASE, it will make use of the GROUP BY

SUM(CASE WHEN rides.ride_status = 'cancelled' THEN 1 ELSE 0 END) as cancelledRides

and the same for completedRides

And move to using JOIN instead of implicit joins

Advertisement

Answer