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 student3_total(s_id,mark) select id, sum(math + social +science) from student3 group by id
The above query will total all marks of each student and then store them in student3_total table. We can use AVG, MAX,MIN commands in place of SUM command to get desire result.
Storing Average Mark
INSERT INTO student3_total(s_id,mark) SELECT id, AVG(math + social +science) from student3 GROUP BY id
create table student3_total select id, AVG(math + social +science) from student3 group by id
Displaying the result with total mark using two tables
Above query will insert student id and sum of the mark to our student3_total table. Now we can join both table and display the final report. This report will collect name, id and individual mark from student3 table and total mark and student id from student3_total table.
Two tables are joined by student id of both tables . Here is the query
SELECT id, name, class, social, science, math, s_id, mark
FROM `student3` , student3_total
WHERE id = s_id
id
name
class
social
science
math
s_id
mark
2
Max Ruin
Three
85
56
85
2
226
3
Arnold
Three
55
40
75
3
170
4
Krish Star
Four
60
50
70
4
180
5
John Mike
Four
60
80
90
5
230
6
Alex John
Four
55
90
80
6
225
7
My John Rob
Fifth
78
60
70
7
208
8
Asruid
Five
85
80
90
8
255
9
Tes Qry
Six
78
60
70
9
208
10
Big John
Four
55
40
55
10
150
Here are our both tables with records for you to test the query.
i have some data , and want to sum Debit and Credit group by account id using select statement not do while or for next statement.
i want result in single line statement.
ACCOUNT NO. DATE PAYMENT_MODE(Debit/Credit) Amount
000001 01/01/2019 Debit 10000
000001 02/01/2019 Debit 20000
000001 03/01/2019 Credit 15000
000001 03/01/2019 Credit 10000
result
Account No Debit Credit
000001 30000 25000
smo1234
19-01-2019
Use Group by to sum based on PAYMENT_MODE(Debit/Credit) Amount.
SELECT ACCOUNT_NO, PAYMENT_MODE,SUM(PAYMENT_MODE ) as MODES FROM table_name WHERE ACCOUNT_NO='000001' GROUP BY PAYMENT_MODE
More on Alter or coy table , add update or delete records in SQL
SQL Video Tutorials