help in inserting data from one table into another table

[punji]

i have to tables result and total, the result table has two field namely student_code and mark and total table has two field namely student_code and total, the total column should contain total marks of a student. so i have written an sql statement to insert data from result table into total table by using a select statement which should take a specific student_code and his/her as a summation and then store it in the total table, but whenever i try the query either failed or just take only one student

When i use this query works but for only one student, whose student_code is 1, but i need to take all the students and they are marks as total

<?php

// copy student student_code and summation of his/her mark  from result table into total table
$query = 'INSERT INTO total
(student_code, total)
SELECT  student_code, SUM(mark)
FROM
result
WHERE
student_code = 1';
$sql = mysql_query($query) or (mysql_error());
?>

And when is use a student code as a variable i get nothing

<?php

// copy student student_code and summation of his/her mark  from result table into total table
$query = 'INSERT INTO total
(student_code, total)
SELECT  student_code, SUM(mark)
FROM
result
WHERE
student_code = "' . $student_code . '"';
$sql = mysql_query($query) or (mysql_error());
?>

Any help will be highy appreciated. thanks in advance.

[cereal]

Hi,

remove the WHERE condition and add GROUP BY student_code to the select statement and it should work fine, but consider that you could use a view, otherwise when you add something else to the results table you have to check and update the totals table.

Examples:

INSERT INTO `results`(`student_code`, `mark`) VALUES(1, '10.20'), (1, '5.30'), (2, '7.30'), (3, '1.25'), (2, '1.25'), (3, '100.99');

INSERT INTO `totals`(`student_code`, `total`) SELECT `student_code`, SUM(`mark`) AS total FROM `results` GROUP BY `results`.`student_code`;

Or:

CREATE VIEW `v_totals` AS SELECT `student_code`, SUM(`mark`) AS `total` FROM `results` GROUP BY `student_code`;

Live examples: http://sqlfiddle.com/#!9/552f4/1

[punji]

thanks cereal it worked.

[punji]

sorry cereal for example i want to get student rank according to they are total marks, so my idea is to create another table with two fields namely student_code and rank and then use the same technique to populate the newly created table, like i did to total table from result table. do you think this is the best idea, because i want store those rank in the database.

[cereal]

Hi,
in my opinion you don't strictly need a new table. To generate a live rank you could add an internal counter to the query, select the results as a subquery and finally display the result set. For example:

SELECT @a := @a + 1 AS `rank`,
       `sub`.`student_code`,
       `sub`.`total`
FROM   (SELECT @a := 0) AS v,
       (SELECT `student_code`,
               SUM(`mark`) AS `total`
        FROM   `results`
        GROUP  BY `student_code`
        ORDER  BY `total` DESC) AS sub; 

It returns something like:

+------+--------------+--------+
| rank | student_code | total  |
+------+--------------+--------+
|    1 |            5 | 428.19 |
|    2 |            3 | 396.83 |
|    3 |            2 | 351.29 |
|    4 |            4 | 271.04 |
|    5 |            1 | 124.56 |
+------+--------------+--------+
5 rows in set (0.00 sec)

But you can do the same in PHP by adding a counter while looping the results, at database level just order by the total, in descending order.

Also, by adding a datetime column (for example created_at) to each mark you could perform rank searches in defined ranges:

SELECT @a := @a + 1 AS `rank`,
       `sub`.`student_code`,
       `sub`.`total`
FROM   (SELECT @a := 0) AS v,
       (SELECT `student_code`,
               SUM(`mark`) AS `total`
        FROM   `results`
        WHERE  `created_at` BETWEEN '2015-03-01' AND '2015-03-31'
        GROUP  BY `student_code`
        ORDER  BY `total` DESC) AS sub;

Live example: http://sqlfiddle.com/#!9/1ff51/1

Then, if you want to save these results, just perform an INSERT ... SELECT query:

• https://dev.mysql.com/doc/refman/5.6/en/insert-select.html

[punji]

thank you so much, sorry for the incovenience.