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 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'); |