CREATE TABLE orders (`city` int, `user` int, `status` string);
INSERT INTO orders (`city`, `user`, `status`) VALUES
(1, 1, 'new'),
(1, 1, 'confirmed'),
(1, 1, 'confirmed'),
(1, 1, 'deleted'),
(1, 1, 'deleted'),
(1, 2, 'new'),
(1, 2, 'confirmed'),
(1, 2, 'deleted'),
(2, 1, 'confirmed'),
(2, 1, 'deleted');

SELECT city, user, COUNT(*) AS total,
       (select distinct count(*) from orders where status = 'new'       group by city, user) AS new,
       (select distinct count(*) from orders where status = 'confirmed' group by city, user) AS confirmed,
       (select distinct count(*) from orders where status = 'deleted'   group by city, user) AS deleted
from orders r
GROUP BY city, user;

SELECT city, user, COUNT(*) AS total,
       SUM(CASE WHEN status = 'new'       THEN 1 ELSE 0 END) AS new,
       SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) AS confirmed,
       SUM(CASE WHEN status = 'deleted'   THEN 1 ELSE 0 END) AS deleted
from orders r
GROUP BY city, user;