CODEWITHSUNDEEP
mysqljoingroup-byaggregate-functionshaving
cbsecommerce
cbsecommerce

Reputation: 47

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

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.

Upvotes: 2

Views: 46

Answers (1)

sgeddes
sgeddes

Reputation: 62851

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.

Upvotes: 1

Related Questions