Home / Tricky Select Queries / Question No: 60

How to get parent table aggregates from the child table and per-child table aggregates from the grandchild table in MySQL?

I have parent->child->grandchild tables. First parent table departments, its child table employees and this also has its child table bonuses. The structure and dummy data of these tables are like below:

CREATE TABLE `departments` ( 
  `dept_id` mediumint(8) unsigned NOT NULL auto_increment,
  `dept_name` varchar(19) default NULL,
  PRIMARY KEY  (`dept_id`)
) ENGINE=MyISAM;

CREATE TABLE `employees` (
  `emp_id` int(10) unsigned NOT NULL auto_increment,
  `emp_name` varchar(38) NOT NULL,
  `emp_dept_id` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY  (`emp_id`)
) ENGINE=MyISAM;

CREATE TABLE `bonuses` (
  `bonus_id` int(11) NOT NULL default '0',
  `bonus_emp_id` int(10) unsigned NOT NULL,
  `bonus_date` date default NULL,
  `bonus_amount` double unsigned NOT NULL,
  PRIMARY KEY  (`bonus_id`)
) ENGINE=MyISAM;

INSERT INTO `departments` (`dept_id`, `dept_name`) 
 VALUES (1, 'HR');
INSERT INTO `departments` (`dept_id`, `dept_name`) 
 VALUES (2, 'Finance');
INSERT INTO `departments` (`dept_id`, `dept_name`) 
 VALUES (3, 'Production');
INSERT INTO `departments` (`dept_id`, `dept_name`) 
 VALUES (4, 'Software');

INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (1, 'Ali', 1);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (2, 'Malik', 1);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (3, 'Ashlay', 2);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (4, 'Nick', 2);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (5, 'Nazir', 3);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (6, 'Ehsan', 3);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (7, 'Sajid', 4);

INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (8, 'Faheem', 4);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (9, 'Majid', 4);
INSERT INTO `employees` (`emp_id`, `emp_name`, `emp_dept_id`) 
 VALUES (10, 'Awais', 4);

INSERT INTO `bonuses` 
 (`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`) 
 VALUES (1, 5, '2007-10-10', 1000);
INSERT INTO `bonuses` 
 (`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`) 
 VALUES (2, 5, '2008-01-01', 1500);
INSERT INTO `bonuses` 
 (`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`) 
 VALUES (3, 8, '2007-06-01', 1000);
INSERT INTO `bonuses` 
 (`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`) 
 VALUES (4, 8, '2007-10-10', 1000);
INSERT INTO `bonuses` 
 (`bonus_id`, `bonus_emp_id`, `bonus_date`, `bonus_amount`) 
 VALUES (5, 8, '2008-02-01', 1000);

I need the results as below:

Department Name        Total Employees         Total Bonuses Achieved
Production                  2                          2
Software                    4                          3

You may use cascading joins as follow, which can serve your purpose:

SELECT de_1.dept_name AS 'Department Name', 
       de_2.total_employee AS 'Total Employees', 
       eb.total_bonuses AS 'Total Bonuses'
FROM (

  SELECT d.dept_id, d.dept_name, e_1.emp_id 
  FROM departments d
  INNER JOIN employees e_1 ON e_1.emp_dept_id = d.dept_id

) AS de_1

INNER JOIN (
  SELECT d.dept_id, COUNT(e2.emp_id) AS total_employee
  FROM departments d
  INNER JOIN employees e2 ON e2.emp_dept_id=d.dept_id
  GROUP BY d.dept_id
) AS de_2 ON de_1.dept_id = de_2.dept_id

INNER JOIN (
  SELECT e3.emp_id, COUNT( b.bonus_id) AS total_bonuses
  FROM employees e3
  INNER JOIN bonuses b ON b.bonus_emp_id=e3.emp_id
  GROUP BY e3.emp_id
) AS eb ON eb.emp_id = de_1.emp_id;