joining two tables and finding the sum of marks for each student
I don't think id field make much sense in the result so I omitted it.
SELECT Student_id,
Subject_id,
Result_id,
year,
SUM(mark)
FROM result_view
GROUP BY Student_id,
Subject_id,
Result_id,
year
How to select students with marks in all three subjects less then 100
You need not to group by your result since there should be one row in each table for every roll_number.
You did the join right. Now just replace group by and having clause with where condition to check whether sum of all three subject's number is less then 100 or not. If it less then hundred then select roll_number and name.
Select a.roll_number, a.name
from student_information a
inner join
examination_marks b
on a.roll_number = b.roll_number
where (subject_one + subject_two + subject_three )< 100;
Create SQL view to show mark for each subject and total of marks for each student
You need conditional aggregation (aka PIVOT
)
CREATE VIEW View_MyHomeworkThatICantBeBotheredToDo
AS
SELECT
Student = stu.name,
Mathematics = SUM(CASE WHEN sub.subjName = 'Mathematics' THEN e.Mark END),
Science = SUM(CASE WHEN sub.subjName = 'Science' THEN e.Mark END),
English = SUM(CASE WHEN sub.subjName = 'English' THEN e.Mark END),
Total = SUM(e.Mark)
FROM Student stu
JOIN Exam e ON e.idStu = stu.idStu
JOIN Subjects sub ON sub.idSub = e.idSub
GROUP BY stu.idStu, stu.name;
GO
How to get distinct count of a column with respective to distinct values of another column in same table?
Your SQL query should be like this:
SELECT COUNT(student_id)
FROM marks
WHERE class_room_id = 10
AND student_id NOT IN (SELECT student_id FROM marks WHERE class_room_id = 10 AND isPassed = 0)
A nested query SELECT student_id FROM marks WHERE class_room_id = 10 AND isPassed = 0
selects all students in a given class who have at least one test with isPassed = 0
. These are the students you DO NOT need.
UPDATE
Okay, after having all the information you've provided in the comments, here is the answer.
The query you're looking for is:
SELECT COUNT(DISTINCT student_id)
FROM tbl2
WHERE student_id NOT IN (SELECT student_id FROM tbl2 WHERE isPassed = 0)
It gets the number of students who passed all the subjects.
The nested query is:
SELECT student_id FROM tbl2 WHERE isPassed = 0
It gets the students who failed at least once.
An SQLFiddle to demonstrate it: http://sqlfiddle.com/#!9/a6baf4/1
How to display all added subject marks or total marks of student in array
Then .. you should set variable as array and append values to it:
<?php
$total_marks = []; // Set default values to make sure it won't throw error for undefined variable below in code
$total_rank = 0;
foreach ($result_detail as $key => $value) {
$total_marks[] = $value['get_marks']; // append values to array
$total_rank += $value['total_rank']; // add rank to current rank
}
echo '<pre>';
var_dump($total_marks);
echo $total_rank;
Notice that I also changed $total_rank
now I add rank each iteration instead of overwriting the value.
Related Topics
Cron Job to Delete Files Created Before a Specific Time
Curl (7): Failed to Connect to Localhost Port 8000: Connection Refused
Codeigniter Cors Policy: No 'Access-Control-Allow-Origin' Error How to Resolve
Laravel 5.2 Post 302 Redirect to Get
Checking If Email Is in MySQL Database in PHP
Convert HTML Form Data into a Pdf File Using PHP
How to Prevent Multiple Inserts When Submitting a Form in PHP
How to Expire a PHP Session After 30 Minutes
How to Get Woocommerce Order Details
How to Pass a PHP Variable Using the Url
How to Convert a 13 Digit Unix Timestamp to Date and Time
How to Submit Multiple PHP Forms While Each Form Has Its Own Submit Button
Failed to Open the System Default Web Browser. Is One Installed - Pgadmin4
How to Replace Newline or \R\N With <Br/>
Laravel Ajax Response Return HTML Elements