CREATE TABLE `clients` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `clients_name_ix` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `unit_types` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unit_types_name_ix` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `units` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `unit_type_id` int(10) unsigned default NULL,
  `client_id` int(10) unsigned NOT NULL,
  `serial_number` varchar(20) NOT NULL,
  `renting` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `units_serial_number_ix` (`serial_number`),
  KEY `units_unit_type_id_ix` (`unit_type_id`),
  KEY `units_client_id_ix` (`client_id`),
  CONSTRAINT `units_unit_type_id__unit_types_id__fk` FOREIGN KEY (`unit_type_id`) REFERENCES `unit_types` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `units_client_id__clients_id__fk` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `clients` (`id`, `name`) VALUES (1, 'Client A'), (2, 'Client B');
INSERT INTO `unit_types` (`id`, `name`) VALUES (1, 'Model A'), (2, 'Model B');
INSERT INTO `units` (`id`, `unit_type_id`, `client_id`, `serial_number`, `renting`) VALUES (1, 1, 1, '101', 0), (2, 1, 1, '102', 0), (3, 1, 1, '103', 1), (4, 2, 1, '104', 1);
INSERT INTO `units` (`id`, `unit_type_id`, `client_id`, `serial_number`, `renting`) VALUES (5, 1, 2, '105', 0), (6, 1, 2, '106', 0), (7, 1, 2, '107', 1), (8, 1, 2, '108', 0);

SELECT
    `cl`.`name`,
    `ut`.`name`,
    COUNT(`u_all`.`id`) AS `unit_count_all`,
    COUNT(`u_sold`.`id`) AS `unit_count_sold`,
    COUNT(`u_renting`.`id`) AS `unit_count_renting`
FROM
    `clients` `cl`

LEFT JOIN
    `units` `u_all`
ON
    `u_all`.`client_id` = `cl`.`id`

LEFT JOIN
    `units` `u_sold`
ON
    `u_sold`.`client_id` = `cl`.`id` AND `u_sold`.`renting` = 0
    
LEFT JOIN
    `units` `u_renting`
ON
    `u_renting`.`client_id` = `cl`.`id` AND `u_renting`.`renting` = 1
    
INNER JOIN
    `unit_types` `ut`
ON
    `ut`.`id` = `u_all`.`unit_type_id`

GROUP BY
    `cl`.`name`,
    `ut`.`name`

ORDER BY
    `cl`.`name`,
    `ut`.`name`

SUM(CASE WHEN renting = 1 THEN 1 ELSE 0 END) AS `count_sold`,
SUM(CASE WHEN renting = 0 THEN 1 ELSE 0 END) AS `count_renting`

SELECT
    `cl`.`name`,
    `ut`.`name`,
    COUNT(`u`.`id`) AS `unit_count_all`,
    SUM(CASE WHEN `u`.`renting` = 0 THEN 1 ELSE 0 END) AS `unit_count_sold`,
    SUM(CASE WHEN `u`.`renting` = 1 THEN 1 ELSE 0 END) AS `unit_count_renting`
FROM
    `clients` `cl`

LEFT JOIN
    `units` `u`
ON
    `u`.`client_id` = `cl`.`id`
    
INNER JOIN
    `unit_types` `ut`
ON
    `ut`.`id` = `u`.`unit_type_id`

GROUP BY
    `cl`.`name`,
    `ut`.`name`

ORDER BY
    `cl`.`name`,
    `ut`.`name`