Join query not working properly for all the elements in the table

112
December 14, 2017, at 01:13 AM

Here is my problem, i have two tables subjects and grades.

  CREATE TABLE `grades` (
  `gradesID` int(11) NOT NULL,
  `studentBook` int(11) DEFAULT NULL,
  `subjectID` varchar(5) DEFAULT NULL,
  `grade` int(5) DEFAULT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `grades` (`gradesID`, `studentBook`, `subjectID`, `grade`) VALUES
(1, 1034, 'AD356', 9),
(2, 1034, 'CS102', 10),
(3, 1034, 'CS103', 9),
(4, 1034, 'CS220', 5)
CREATE TABLE `subjects` (
  `subjectID` varchar(5) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `espb` smallint(6) DEFAULT NULL,
  `number_of_classes_per_week` smallint(6) DEFAULT NULL,
  `number_of_practices_per_week` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `subjects`
--
INSERT INTO `subjects` (`subjectID`, `name`, `espb`, `number_of_classes_per_week`, `number_of_practices_per_week`) VALUES
('AD356', '3D modeling Maya', 10, 3, 3),
('CS101', 'Introduction to object-oriented programming', 10, 3, 4),
('CS102', 'JAVA 2', 10, 3, 4),
('CS103', 'Algorithms', 8, 3, 3),

I need to make Join query to find all for all the subjects number of students that tried to take the exam(no matter they passed or failed).And i need to show as a result: subjects.subjectID,subjects.name,totall number of classes and practices for every subject, number of students that took the exam(no matter passed or failed). So this is my attempt:

SELECT 
  subjects.subjectID,
  subjects.name,
  (subjects.number_of_classes_per_week+subjects.number_of_practices_per_week) AS 'Totall number of classes per week',
  COUNT(grades.subjectID) AS 'Number of students that tried to pass'
FROM 
  subjects,
  grades 
WHERE 
  subjects.subjectID = grades.subjectID AND 
  grade >= 5;

but i get only first subjectID his number of classes for that subject and a total number of students for all the subjects The outcome that i would like to have is like this:

subjectID     name                           Totall number of classes per week                      Number of students that tried to pass
AD356    3D modeling Maya                  6              10
CS101   Introduction to object-oriented programming 7      4
CS102  'JAVA 2'                            7               5   
CS103    Algorithms                        6               4
Answer 1

You need to tell MySQL what to count - in other words, which field should be used as a grouping identifier to count over.

In your case, that would be the subjectID, as you want to count all the grades (with a grade above or equal to 5) for each subject.

So you need to add after your WHERE statement:

GROUP BY subjectID

And then you should be done.

Answer 2

Your are using count() aggregate functions without group by, so mysql returns only one row. Try this (it may help you):

SELECT 
  subjects.subjectID,
  subjects.name,
  (subjects.number_of_classes_per_week+subjects.number_of_practices_per_week) AS 'Totall number of classes per week',
  COUNT(distinct grades.studentBook) AS 'Number of students that tried to pass'
FROM 
  subjects
JOIN grades ON grades.subjectID = subjects.subjectID
GROUP BY subjects.subjectID
READ ALSO
$. syntax for jquery

$. syntax for jquery

I have a code snippet I came across when learning jQueryI am familiar with jQuery's basic selector syntax $('element'), but I do not know what the $

146
Convert program into function, procedural and object oriented programming paradigm [on hold]

Convert program into function, procedural and object oriented programming paradigm [on hold]

I am trying to write the following code using three different programming paradigms(object oriented, functional, procedural) to practice my python and I am trying to figure out how to go about itHow can I rewrite this code in the 3 other paradigms?

129
Put image and content into jQuery dialog box

Put image and content into jQuery dialog box

I want to make a dialog box where the user can see the image that he/she has just uploadedThe file uploading systems works fine but when I want to put the image into the dialog box dynamically only an empty box appears

9
Node bot configuration issue

Node bot configuration issue

Bot running on local ubuntu 1604 server begind the Nginx

206