how to apply having, group by clause while using join for MySQL table

Question

I have 2 tables in MySQL - Question Table

QuestionID | QuestionName
-----------|---------------------------------------
1          | How is your faculty communication
-----------|---------------------------------------
2          | How is your study matrial
-----------|---------------------------------------
3          | How your faculty language
-----------|---------------------------------------
4          | Is your faculty cooperative
-----------|---------------------------------------
5          | Is your practical time is enough
-----------|---------------------------------------
6          | Your class starts on time
-----------|---------------------------------------
7          | In practical your doubts are cleared properly
-----------|---------------------------------------
8          | What will you rate for computer
-----------|---------------------------------------
9          | In Theory your questions are replied properly
-----------|---------------------------------------
10         |Your faculty is comes on time
-----------|---------------------------------------

Result Table

RID QID Faculty         Student         Sem Excell  Better  Good Poor
1   1   Ankush          Vishal Deb      III 1       0       0    0
2   2   Ankush          Vishal Deb      III 0       1       0    0
3   3   Ankush          Vishal Deb      III 0       0       1    0
4   4   Ankush          Vishal Deb      III 0       0       0    1
5   5   Ankush          Vishal Deb      III 0       0       1    0
6   6   Ankush          Vishal Deb      III 0       1       0    0
7   7   Ankush          Vishal Deb      III 1       0       0    0
8   8   Ankush          Vishal Deb      III 0       1       0    0
9   9   Ankush          Vishal Deb      III 0       0       1    0
10  10  Ankush          Vishal Deb      III 0       0       0    1
11  1   Mahendra Singh  Mohit Chauhan   III 0       1       0    0
12  2   Mahendra Singh  Mohit Chauhan   III 0       0       1    0
13  3   Mahendra Singh  Mohit Chauhan   III 0       1       0    0
14  4   Mahendra Singh  Mohit Chauhan   III 0       0       0    1
15  5   Mahendra Singh  Mohit Chauhan   III 0       1       0    0
16  6   Mahendra Singh  Mohit Chauhan   III 0       0       1    0
17  7   Mahendra Singh  Mohit Chauhan   III 1       0       0    0
18  8   Mahendra Singh  Mohit Chauhan   III 0       0       0    1
19  9   Mahendra Singh  Mohit Chauhan   III 0       1       0    0
20  10  Mahendra Singh  Mohit Chauhan   III 0       0       0    1

Now I need to display the records of a particular faculty from a particular semester but report should display total number of excellent, better, good and poor scored by that faculty from the students of that semester.

For example if 5 student from semester III submitted feedback for Ankush then report should come like - I am giving example for 4 questions

---------------------------------------------------------------------+
Question                           |Excellent | Better | Good | Poor |
-----------------------------------|----------|--------|------|------|
How is your faculty communication  |    3     |   2    |   0  |  0   |
-----------------------------------|----------|--------|------|------|
How is your study matrial          |    1     |   1    |   3  |  0   |
-----------------------------------|----------|--------|------|------|
How your faculty language          |    0     |   1    |   3  |  1   |
-----------------------------------|----------|--------|------|------|
Is your faculty cooperative        |    1     |   1    |   2  |  1   |
-----------------------------------|----------|--------|------|------|

I tried this query but this is not what I need

SELECT q.questionname, r.excellent, r.better, r.good, r.poor
FROM question q, result r
WHERE r.facultyid =  'Ankush'
AND r.Semester =  'III'
AND q.questionID = r.questionID

also tried

Select q.questionname, sum(r.excellent),sum(r.better),sum(r.good),sum(r.poor)
from question q,result r 
where r.facultyid='Ankush' and r.Semester='III' and q.questionID=r.questionID;

But not successful. Kindly guide me how can I get my result. Thank you in advance.

sgeddes · Accepted Answer · 2016-03-05T21:05:40.573

1

Your 2nd query is extremely close -- you just left off the group by clause:

Select q.questionname, sum(r.excellent),sum(r.better),sum(r.good),sum(r.poor)
from question q 
    inner join result r on q.questionID=r.questionID
where r.facultyid='Ankush' 
    and r.Semester='III' 
group by q.questionname

Also please note this uses an explicit join. In general I would recommend not using commas in the from clause.

edited Mar 05 '16 at 21:05

answered Mar 05 '16 at 20:56

sgeddes

62,311
6
61
83

Thankx a lot, but why you recommended not to use commas in from clause. – cbsecommerce Mar 06 '16 at 02:31
2

@cbsecommerce -- here are a few good posts why: http://stackoverflow.com/a/5654338/1073631; http://programmers.stackexchange.com/questions/78225/using-join-keyword-or-not; https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – sgeddes Mar 06 '16 at 02:36

how to apply having, group by clause while using join for MySQL table

1 Answers1