multiple count from multiple tables and also group by grade

Question

i have table with student_uid,grade,test_name as columns i want to count how many got each grade..for this

SELECT a.grade,COUNT(a.grade) AS count1 
FROM 2015_2016_x_english_grades AS a 
where test_name='ut1_marks' 
GROUP BY grade

for single table worked how to do it for more than one table

my query:

SELECT a.grade, COUNT(a.grade),b.grade,COUNT(b.grade) 
FROM 2015_2016_x_english_grades a 
INNER JOIN 2015_2016_x_hindi_grades b ON a.grade=b.grade 
WHERE a.test_name = b.ut1_marks='ut1_marks' 
GROUP BY a.grade,b.grade 

what is wrong in this? i also tried this SELECT a.grade,COUNT(a.grade),(SELECT COUNT(b.grade)FROM 2015_2016_x_biology_grades b where b.test_name='ut1_marks' GROUP BY b.grade)as count1 FROM 2015_2016_x_biology_grades a where test_name='ut1_marks' GROUP BY a.grade it says [Err] 1242 - Subquery returns more than 1 row

Answer 1

Do the counting in subqueries, and join the subqueries.

SELECT e.grade, english_count, hindi_count
FROM (SELECT grade, COUNT(*) AS english_count
      FROM 2015_2016_x_english_grades
      WHERE test_name = 'ut1_marks'
      GROUP BY grade) AS e
JOIN (SELECT grade, COUNT(*) as hindi_count
      FROM 2015_2016_x_hindi_grades
      WHERE test_name = 'ut1_marks'
      GROUP BY grade) AS h
ON e.grade = h.grade

Or if there's a unique key in each table, you can do:

SELECT e.grade, COUNT(DISTINCT e.id) AS english_count, COUNT(DISTINCT h.id) AS hindi_count
FROM 2015_2016_x_english_grades AS e
JOIN 2015_2016_x_hindi_grades AS h ON e.grade = h.grade AND e.test_name = h.test_name
WHERE e.test_name = 'ut1_marks'
GROUP BY e.grade

Note that both of these queries will only show a grade if it exists in both tables. To get grades that only exist in one table, you need a FULL OUTER JOIN, but MySQL doesn't have this operation. See

Full Outer Join in MySQL

for how to emulate them.