How to transition this MySql subquery to a join query?

Question

I've created a mysql query which includes a subquery which I'd like to convert to a join query.

The reason I'm using a subquery is I need to have the where condition at the end applied to result of the that subquery. Here's the query:

 select date, system_name, device_name, valid_read_rate from 
(SELECT  left(from_unixtime(c.capture_time/1000),10) as date,  a.name as 'system_name', b.name as 'device_name', c.read_count, c.valid_object_count, 
if(valid_object_count = 0,'00.00%',concat(format(read_count/valid_object_count*100,2),'%')) as valid_read_rate, read_count/valid_object_count*100 as valid_read_rate_unformatted
FROM  sick_il_services.system_config a
join sick_il_services.system_device b
on a.id = b.system_id
join sick_il_aap.daily_device_read_rate c
on b.id = c.device_id
where 1=1
group by c.capture_time,a.name, b.name
order by c.capture_time, a.name, b.name) as sub
where sub.valid_read_rate_unformatted < 100

I'm just having trouble getting untracked on this. Any advice welcome.

Answer 1

As long as whatever format you are using allows you to filter columns later, the subquery wasn't particularly doing much from what I can tell. This should cover what you are doing, however, without a CREATE TABLE syntax, I cannot verify the answer.

SELECT left(from_unixtime(c.capture_time / 1000), 10)                    as date,
       a.name                                                            as 'system_name',
       b.name                                                            as 'device_name',
       c.read_count,
       c.valid_object_count,
       if(valid_object_count = 0, '00.00%',
       concat(format(read_count / valid_object_count * 100, 2), '%')) as valid_read_rate
FROM sick_il_services.system_config a
         join sick_il_services.system_device b on a.id = b.system_id
         join sick_il_aap.daily_device_read_rate c on b.id = c.device_id
where (c.read_count / c.valid_object_count * 100) < 100
group by c.capture_time, a.name, b.name
order by c.capture_time, a.name, b.name

---

Updated from the comments

In your comment, you mention that you might be looking for a summed average from your grouping. In that case, you might be looking to do something along the following

SELECT left(from_unixtime(c.capture_time / 1000), 10)                    as date,
       a.name                                                            as 'system_name',
       b.name                                                            as 'device_name',
       c.read_count,
       c.valid_object_count,
       if(valid_object_count = 0, '00.00%',
       concat(format(read_count / valid_object_count * 100, 2), '%')) as valid_read_rate
FROM sick_il_services.system_config a
         join sick_il_services.system_device b on a.id = b.system_id
         join sick_il_aap.daily_device_read_rate c on b.id = c.device_id
group by c.capture_time, a.name, b.name
having (sum(read_count)/sum(valid_object_count)) < 1
order by c.capture_time, a.name, b.name