mysql - SQL join query with count operation -

hey have database has following tables

students table:

+------------+-----------+----------+------------+ | studeni_id | firstname | lastname | contact    | +------------+-----------+----------+------------+ |          1 | deen      | nute     |   85124512 | |          2 | helen     | jude     | 2147483647 | |          3 | howard    | mindy    |    8965123 | +------------+-----------+----------+------------+ 

first_exam table:

+------------+--------------+-------+------+------+ | studeni_id | subject_name | score | year | term | +------------+--------------+-------+------+------+ |          1 | english      |    54 | 2015 |    1 | |          1 | math         |    48 | 2014 |    2 | |          1 | history      |    85 | 2015 |    1 | |          2 | english      |    59 | 2015 |    1 | |          2 | math         |    65 | 2015 |    1 | |          3 | english      |    59 | 2015 |    1 | |          2 | science      |    41 | 2015 |    1 | +------------+--------------+-------+------+------+ 

last_exam table:

+------------+--------------+-------+------+------+ | studeni_id | subject_name | score | year | term | +------------+--------------+-------+------+------+ |          1 | english      |    75 | 2015 |    1 | |          2 | english      |    86 | 2015 |    1 | |          3 | history      |    72 | 2015 |    1 | +------------+--------------+-------+------+------+ 

i trying count of exams taken each student in year 2015 , term 1 here queries have tried , outputs:

select first_exam.studeni_id, student.firstname, student.lastname,  count(first_exam.subject_name)as firste, count(last_exam.subject_name)as last_e  student,first_exam,last_exam   student.studeni_id=first_exam.studeni_id=last_exam.studeni_id  , first_exam.year=2015 , first_exam.term=1  , last_exam.year=2015 , last_exam.term=1  group first_exam.studeni_id; 

output:

+------------+-----------+----------+--------+--------+ | studeni_id | firstname | lastname | firste | last_e | +------------+-----------+----------+--------+--------+ |          1 | deen      | nute     |      2 |      2 | |          2 | helen     | jude     |      3 |      3 | |          3 | howard    | mindy    |      1 |      1 | +------------+-----------+----------+--------+--------+ 

and

select first_exam.studeni_id, student.firstname, student.lastname, count(first_exam.subject_name)as first_e,count(last_exam.subject_name)as last_e first_exam  inner join student  on student.studeni_id=first_exam.studeni_id  inner join last_exam  on first_exam.studeni_id=last_exam.studeni_id  first_exam.year=2015 , first_exam.term=1 , last_exam.year=2015 , last_exam.term=1 group first_exam.studeni_id ; 

output:

+------------+-----------+----------+---------+--------+ | studeni_id | firstname | lastname | first_e | last_e | +------------+-----------+----------+---------+--------+ |          1 | deen      | nute     |       2 |      2 | |          2 | helen     | jude     |       3 |      3 | |          3 | howard    | mindy    |       1 |      1 | +------------+-----------+----------+---------+--------+ 

they have errors in output can please me solve issue....

i join student table , first exam table, , sum rows when year , term said, gaurentee students student table come, if have not done test. union same format last exam.

why there 2 tables same structure, can store them in same table.

then sum value union of 2 tables group student id .

the below query should work

select studeni_id, sum(cnt) ( select studeni_id , sum(case when year = 2015  1 else 0 end) cnt students left join first_exam on students.studeni_id  = first_exam.studeni_id group students.studeni_id union select studeni_id , sum(case when year = 2015  1 else 0 end) cnt students left join last_exam on students.studeni_id  = last_exam.studeni_id group students.studeni_id ) group studeni_id