Count of multiple columns from a table in mysql

[vasanthan pv]

Hi



I have total 3 tables

tbl_projects,tbl_bug,tbl_bug_history


i need to display total of 3 counts for each projects.

1.total bug for each projects- this is from tbl_bug

2. total count of "invalid", total count of "duplicate"-- this is from bug history



Output should be in the following sample format

------------------------------------------------
project name | total bug | invalid | duplicate |

------------------------------------------------
project-one | 5 | 6 | 7 |
-------------------------------------------------

Please help me table structure is define below





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

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
(2, 1, 'invalid'),
(3, 2, 'invalid'),
(6, 3, 'duplicate'),
(7, 4, 'feedback'),
(10, 5, 'duplicate'),
(11, 6, 'duplicate'),
(12, 6, 'invalid'),
(13, 7, 'feedback'),
(14, 7, 'normal'),
(15, 8, 'duplicate'),
(16, 8, 'normal'),
(18, 9, 'feedback'),
(19, 10, 'invalid'),
(20, 10, 'feedback');



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