SQL PHP HTML ASP JavaScript articles and free scripts to download
 
 

Calculating and storing SUM of a column in another table

Some time we have to collect the SUM , AVG or any other type of data from one table and store them in another table. Say for example we want to store sum of all marks of each student in a different table. We conducted different exams for students and now we will be adding all the marks for each student and store them in a different table. We will be using GROUP BY command to find out sum of all the marks or each student. Here is the SQL to do this.

insert into s_mark(s_id,mark) select s_id, sum(test1) from s_test group by s_id

The above query will total all marks of each student and then store them in s_marks table. You can use AVG, MAX,MIN commands in place of SUM command to get desire result. Here is the one for AVG marks

insert into s_mark(s_id,mark) select s_id, AVG(test1) from s_test group by s_id

Here are our both tables for you.

CREATE TABLE `s_mark` (
  `s_id` int(3) NOT NULL,
  `mark` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `s_mark`
--
INSERT INTO `s_mark` VALUES (1, 7);
INSERT INTO `s_mark` VALUES (2, 6);
-- --------------------------------------------------------
--
-- Table structure for table `s_test`
--
CREATE TABLE `s_test` (
  `s_id` int(3) NOT NULL,
  `test1` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Discuss this tutorial at forum

List of SQL Tutorials


Further readings
Creating a new table by using data from one table
Copying data from one table to another table
Updating another table with data from main table
Update SQL commands
Inserting SUM, AVG data from one table column to other using group by command
Create table query with if exists sql with php script
 
Scripts
PHP
JavaScript
HOME
SQL Tutorial List
SQL (Home)
SQL Commands
AVG
Alter Table
Between
Count
Copy Table
Create Table
Delete
Distinct
Group by
Having
Insert
Inner Join
IN
Left join
Limit
Like
MAX
MIN
Order By
OR AND
Rand
Replace
Rename Table
Select Query
Sum
Union
Update
Where
Subscribe
Submit your email address and receive article and product notifications. Your email is safe with us.