MySQL Grouping Team Tasks by Total and Completed-CodePudding

I have 2 tables Team and Tasks. Tasks are assigned to each team, I want a SQL (MySQL) query to get the Tasks grouped by Team based on Total and Completed Task count as illustrated below. I also want to have zero values if the count is zero in any case (both total and completed counts)

Team Table Structure

Team_ID int,
Team_Name varchar(50)

Tasks Table

Task_ID int,
Task_Name varchar(100),
Team_ID int (fk)
Completed bit (boolean Yes/No)

Expected Results table

Team Name	Total Tasks	Completed Tasks Count
Marvels	6	4
Directors	4	2
Commanders	2	0
Veterans	0	0
Newbies	3	1

Addding Create and Insert Code

CREATE TABLE `teams` (
  `teamId` int(11) NOT NULL AUTO_INCREMENT,
  `teamName` varchar(50) NOT NULL,
  PRIMARY KEY (`teamId`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;


INSERT INTO `teams` (`teamName`) VALUES ('Marvels');
INSERT INTO `teams` (`teamName`) VALUES ('Directors');
INSERT INTO `teams` (`teamName`) VALUES ('Commanders');
INSERT INTO `teams` (`teamName`) VALUES ('Vetrans');
INSERT INTO `teams` (`teamName`) VALUES ('Newbies');


CREATE TABLE `tasks` (
  `task_id` int(11) NOT NULL AUTO_INCREMENT,
  `task_name` varchar(100) NOT NULL,
  `team_id` int(11) NOT NULL,
  `completed` tinyint(1) NOT NULL,
  PRIMARY KEY (`task_id`),
  KEY `team_id` (`team_id`),
  CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `teams` (`teamId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Water Plants', '1', '1');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Feed Cat', '3', '0');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Go Cycling', '5', '0');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Prepare Food', '2', '0');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Clean House', '2', '1');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Reapair Car', '1', '0');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Read Book', '3', '0');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Video Album', '1', '1');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Audio Album', '1', '0');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Walk Dog', '5', '1');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Get Grocery', '2', '1');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Plumbing Work', '1', '1');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Audtion', '2', '0');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Go Fishing', '5', '0');
INSERT INTO `tasks` (`task_name`, `team_id`, `completed`) VALUES ( 'Host meeting', '1', '1');

CodePudding user response：

SELECT      
        tp.teamName 'Team Name',     
        IFNULL(final.cnt, 0) 'Total Tasks',   
        IFNULL(comp, 0) 'Completed Tasks Count'   
FROM   
teams tp
    LEFT OUTER JOIN
(SELECT 
    t.teamId,
        t.teamName,
        COUNT(tk.task_id) cnt,
        IFNULL(b.Comp, 0) comp
FROM
    teams t
JOIN tasks tk ON t.teamId = tk.team_id
LEFT OUTER JOIN (SELECT 
    tkp.team_id, COUNT(tkp.task_id) Comp
FROM
    tasks tkp
WHERE
    tkp.Completed = '1'
GROUP BY tkp.team_id) b ON b.team_id = t.teamId
GROUP BY t.teamId) final ON tp.teamId = final.teamid;