uzaif uzaif - 5 months ago 11
SQL Question

how can i extract total forum_question and fourm_answer count as subject wise in mysql i have below mysql structure

forum_question :

CREATE TABLE IF NOT EXISTS `forum_question` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`question` text NOT NULL,
`subject_id` int(11) NOT NULL,
`student_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `subject_id` (`subject_id`,`student_id`),
KEY `student_id` (`student_id`)
)


forum_answer table

CREATE TABLE IF NOT EXISTS `forum_answer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`answer` text NOT NULL,
`question_id` int(11) NOT NULL,
`faculty_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `question_id` (`question_id`,`faculty_id`),
KEY `faculty_id` (`faculty_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


and subject table

CREATE TABLE IF NOT EXISTS `subject` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(150) NOT NULL
)

Answer

Try this edited query, I am sure this will work

SELECT 
    COUNT(que.id) AS totalQuestions, 
    (SELECT COUNT(DISTINCT(forum_answer.id)) 
    FROM 
        forum_answer 
    WHERE 
        forum_answer.question_id = que.id 
    GROUP BY que.id) 
    AS totalAns 
FROM 
    forum_question que 
    INNER JOIN 
    subject sub 
    ON (que.subject_id = sub.id) 
 WHERE 
    que.id > 0 
 GROUP BY sub.id