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

116
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 $

157
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?

136
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

11
Node bot configuration issue

Node bot configuration issue

Bot running on local ubuntu 1604 server begind the Nginx

213