select * from
    (
      select p.id, trim(p.name) as name, (
        select case when sum(total_quantity) > 0 then 1 else 0 end as total
        from product_details pd
        where pd.product_id = p.id
        and pd.city = "kota-surabaya"
        and pd.group_type  = "grosir"
        and (select count(pdt.id) from product_detail_tierings pdt where pdt.product_detail_id = pd.id) > 0
      ) as stock_marker,
        (
          SELECT (SUM(oi.quantity) / COUNT(oi.id)) FROM order_items as oi
          WHERE oi.product_id=p.id
          and oi.id in
          (216122,  216121, 216120, 216119, 216118, 216117, 216116, 216115, 216114, 216113, 216112, 216111, 216110, 216109, 216108, 216107, 216106, 216105, 216104, 216103, 216102, 216098, 216097, 216096, 216095, 216094, 216093, 216092, 216090, 216089, 216088, 216087, 216086, 216085, 216084, 216082, 216081, 216080, 216079, 216078, 216077, 216076, 216074, 216073, 216072, 216071, 216070, 216069, 216068, 216067, 216066, 216065, 216064, 216063, 216062, 216061, 216060, 216059, 216058, 216057, 216056, 216055, 216054, 216053, 216052, 216051, 216050, 216049, 216048, 216047, 216046, 216045, 216044, 216043, 216042, 216041, 216040, 216039, 216038, 216037, 216036, 216035, 216034, 216033, 216032, 216031, 216030, 216029, 216028, 216027, 216026, 216025, 216024, 216023, 216022, 216021, 216020, 216019, 216018, 216017, 216016, 216015, 216014, 216013, 216012, 216011, 216010, 216009, 216008, 216007, 216006, 216005, 216004, 216003, 216002, 216001, 216000, 215999, 215998, 215996, 215995, 215994, 215993, 215992, 215991, 215990, 215989, 215988, 215987, 215986, 215985, 215982, 215981, 215980, 215979, 215978, 215977, 215976, 215975, 215974, 215973, 215972, 215971, 215970, 215969, 215968, 215967, 215966, 215965, 215964, 215963, 215962, 215961, 215960, 215959, 215958, 215957, 215956, 215955, 215954, 215953, 215952, 215951, 215950, 215949, 215948, 215947, 215946, 215945, 215944, 215943, 215942, 215941, 215940, 215938, 215937, 215936, 215935, 215934, 215933, 215932, 215931, 215930, 215929, 215928, 215927, 215926, 215925, 215923, 215922, 215921, 215920, 215918, 215917, 215916, 215915, 215914, 215913, 215912, 215911)
        ) as bestseller,
      c.id as category_id, c.name as category_name, p.created_at, p.updated_at
      from products p
      join categories c on p.category_id = c.id
      where p.status = 1 and
      (
        select count(pd2.id) from product_details pd2
        left join product_detail_tierings pdt2 on pdt2.product_detail_id = pd2.id
        where pd2.product_id = p.id
        and pd2.city = 'kota-surabaya'
        and pd2.group_type = "grosir"
        and pdt2.selling_price > 0
        and pd2.total_quantity > 0
      ) > 0
       ) list order by stock_marker desc,  `bestseller` asc  limit 20 offset 0;