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

# 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_viewGROUP 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 ainner joinexamination_marks b       on a.roll_number = b.roll_numberwhere (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_MyHomeworkThatICantBeBotheredToDoASSELECT    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 stuJOIN Exam e ON e.idStu = stu.idStuJOIN Subjects sub ON sub.idSub = e.idSubGROUP 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 marksWHERE class_room_id = 10AND 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 tbl2WHERE 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.