Converting subquery to Join (MySQL)

Question

Is it possible to convert these subqueries to join clause?

SELECT ods.*,
            (SELECT COUNT(*) FROM orders) tot_ords,
            (SELECT COUNT(*) FROM orders WHERE order_confirmed = 'yes') yes_ords,
            (SELECT COUNT(*) FROM orders WHERE order_confirmed = 'no') no_ords
FROM orders ods
ORDER BY ods.order_confirmed = 'yes' DESC

Answer 1

Without joins and without subqueries, using analytics:

SELECT ods.*,
       COUNT(*) OVER()          tot_ords,
       COUNT(case when order_confirmed = 'yes' then 1 else null end) OVER() yes_ords,
       COUNT(case when order_confirmed = 'no' then 1 else null end) OVER() no_ords
FROM orders ods
ORDER BY ods.order_confirmed = 'yes' DESC

Using join:

   SELECT ods.*, 
          s.tot_ords,
          s.yes_ords,
          s.no_ords 
     FROM orders ods
          cross join (select 
                             COUNT(*) tot_ords,
                             COUNT(case when order_confirmed = 'yes' then 1 else null end)  yes_ords,        
                             COUNT(case when order_confirmed = 'no' then 1 else null end)  no_ords 
                       from orders
                       ) s
    ORDER BY ods.order_confirmed = 'yes' DESC