在mysql查询中使用组获取学校计数和年级计数

Question

我想得到总学校的总数，包括在那所学校的总数，如果学校计数，它不应该考虑重复的学校身份。包括学校一年级和二年级在内，我只想用一个查询就可以得到预期的输出。

表：

school_id      grade

  1             1

  2             1


  1             3

  1             4

预期产出：

1) school count=2

2) grade count for individual school like

    school_id   grade_count

      1           3
      2           1

3)  total grade count  (school 1+school2)=4

我使用了这个Mysql查询，但是它没有按照要求来获得所有三个输出。

Mysql查询：

select count(*) as total_school,count(grade) from (SELECT grade FROM schools group by school_id)X 

Answer 1

SELECT 
  `school_id`,
  (SELECT COUNT(DISTINCT `school_id`) FROM `school_grade`) as different_school_count,
  COUNT(*) as school_count,
  COUNT(DISTINCT `grade`) as grade_count,
  (SELECT COUNT(*) FROM `school_grade`) as total_grade_count
FROM
  `school_grade`
GROUP BY `school_id`

产出：

| school_id | different_school_count | school_count | grade_count | total_grade_count |
|-----------|------------------------|--------------|-------------|-------------------|
|         1 |                      2 |            3 |           3 |                 4 |
|         2 |                      2 |            1 |           1 |                 4 |

http://sqlfiddle.com/#!9/9132c7/16