1

Got some problem after using inner join, this is my query.

select sum(total)  as total,id
from
(
select * from midsemester union
select * from endsemester
) as vij
group by id

This is the table:

Table name: midsemester

ID  Grade
----------
1    10
2    30
3    40

Table name: endsemester


ID   Grade
----------
1    30
2    40
3    20

and i need to sum these table to new table called total. This is the results that i was hoping.

Table name: total

ID   Grade
----------
1     40
2     70
3     60

I actually just need to sums up the grade's value using the id for the 3rd table. And tried several times using inner join, it's working. But when i re-checked the 3rd table again, the record was empty. Would appreciate any help, thanks! :)

andhikaribrahim
  • 341
  • 6
  • 24
  • You need to union all. Check: http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all – Hozikimaru Dec 05 '14 at 06:34
  • I saw it before posting this question and tried using union all, but it's still the same. Anyway, thanks man :) – andhikaribrahim Dec 05 '14 at 06:44
  • I would recommend not having two different tables that store the same thing. You should combine `midsemester` and `endsemester` into one grade-related table and introduce a column that indicates which part of the semester the grade is for. – siride Dec 05 '14 at 13:55

3 Answers3

1

Try this: 

INSERT INTO total (Id, Grade)
SELECT id, SUM(grade) AS total
FROM (SELECT id, grade FROM midsemester 
      UNION ALL 
      SELECT id, grade FROM endsemester
     ) AS vij
GROUP BY id
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • yours was summing the entire field my friend, what i need is to sum it up one by one. But anyway thanks for helping :) – andhikaribrahim Dec 05 '14 at 06:36
  • i need each record get summed, not entirely. Please see the 3rd table on my post. That's what i really wants for the result. No offense, but when i use your query the result was like this: ID = 12, and the Grade = 60 – andhikaribrahim Dec 05 '14 at 06:41
  • Oh my bad. I'm really sorry about the misunderstanding, so i need to join the midsemester and endsemester grades and put it on the total table. And it seems like when i use some other query it's not inserting to the 3rd tables. – andhikaribrahim Dec 05 '14 at 07:08
1

Try below code:

select a.id,(a.grade+b.grade) as sum 
into total
from midsemester a 
left join endsemester b 
    on a.id = b.id
enam
  • 1,179
  • 1
  • 10
  • 24
  • How do i put this on the 3rd table? it's not inserted to the records bro. – andhikaribrahim Dec 05 '14 at 07:03
  • create tabel total (id integer , Grade integer); insert into total select a.id,(a.grade+b.grade) as sum from midsemester a left join endsemester b on a.id = b.id; – Sagar Joon Dec 05 '14 at 07:15
1

try this 

insert into total(ID,Grade) select midsemester.ID,(midsemester.grade+endsemester.grade)as total from midsemester
inner join endsemester
on midsemester.ID=endsemster.ID

Use order by ID if u want data according to ID increasing order

Arunprasanth K V
  • 20,733
  • 8
  • 41
  • 71