Calculate Student Total Marks and Insert That Total in Another Table With the Id of That Student Together

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



Leave a reply



Submit