Query SQL with spread table

There is a table with information on orders.

While it turns out to do only this:

SELECT count(*) as 'Заказы' , order_stat as 'Статус' , sum(cost_profit) as 'Доход' , sum(cost_drop) as 'Затраты' FROM `drop`WHERE DATE(data_order)='2016-09-07' GROUP BY order_stat 

Tell me where to "dig" to get a table of the form: Date, Confirmed, Canceled, Sent, Total , you need to understand how to use subqueries.

Thanks to the prompts everything worked out.

 SELECT DATE(data_order) as 'Дата заказа', count(*) as 'заказы', COUNT( CASE WHEN order_stat LIKE '%Отменен%' THEN 1 ELSE NULL END ) AS 'отменен', COUNT( CASE WHEN order_stat LIKE '%Подтвержден%' THEN 1 ELSE NULL END ) AS 'подтвержденн', COUNT( CASE WHEN order_stat LIKE '%Отправленный%' THEN 1 ELSE NULL END ) AS 'отправленный', COUNT( CASE WHEN order_stat LIKE '%Оплаченный%' THEN 1 ELSE NULL END ) AS 'оплаченный', sum(cost_profit) as 'Доход', sum(cost_drop) as 'Затраты' FROM `drop` GROUP BY DATE(data_order) ORDER BY `drop`.`data_order` DESC