Count of multiple fields in a join query in mysql
This is the query i used to fetch count of multiple fields from my table.I used four times select query inside a query before doing join
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select proj.id,proj.projectname,count(bugtbl.id) as totalbug,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'invalid' ) as invalidcount,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'feedback') as feedbackcount,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'duplicate') as duplicatecount,(select count(id) from tbl_bug_history where bug_id = bugtbl.id and status = 'normal') as normalcount from tbl_projects proj left join tbl_bug bugtbl on bugtbl.project_id=proj.id group by proj.projectname |
you can see the database structure and query result as below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
CREATE TABLE IF NOT EXISTS `tbl_projects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `projectname` varchar(250) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `tbl_projects` -- INSERT INTO `tbl_projects` (`id`, `projectname`) VALUES (1, 'project-one'), (2, 'project-two'), (3, 'project-three'), (4, 'project-four'); CREATE TABLE IF NOT EXISTS `tbl_bug_history` ( `id` int(10) NOT NULL AUTO_INCREMENT, `bug_id` int(10) NOT NULL, `status` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ; -- -- Dumping data for table `tbl_bug_history` -- INSERT INTO `tbl_bug_history` (`id`, `bug_id`, `status`) VALUES (1, 1, 'invalid'), (2, 1, 'invalid'), (3, 2, 'invalid'), (4, 2, 'invalid'), (5, 3, 'duplicate'), (6, 3, 'duplicate'), (7, 4, 'feedback'), (8, 4, 'feedback'), (9, 5, 'duplicate'), (10, 5, 'duplicate'), (11, 6, 'duplicate'), (12, 6, 'invalid'), (13, 7, 'feedback'), (14, 7, 'normal'), (15, 8, 'duplicate'), (16, 8, 'normal'), (17, 9, 'feedback'), (18, 9, 'feedback'), (19, 10, 'invalid'), (20, 10, 'feedback'); CREATE TABLE IF NOT EXISTS `tbl_bug` ( `id` int(10) NOT NULL AUTO_INCREMENT, `project_id` int(10) NOT NULL, `bugname` varchar(250) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `tbl_bug` -- INSERT INTO `tbl_bug` (`id`, `project_id`, `bugname`) VALUES (1, 1, 'first-bug'), (2, 1, 'second-bug'), (3, 1, 'bug-third'), (4, 1, 'bug-four'), (5, 1, 'bug-give'), (6, 1, 'master-bug'), (7, 2, 'error-notice'), (8, 3, 'invalid bug'), (9, 4, 'insufficinet memory'), (10, 4, 'hello bug'); |