时间戳

首页 > 解决方案 > 获取每个 id 组的计数总和

mysql - 获取每个 id 组的计数总和

问题描述

我有一个数据集，其中不同的学校在不同的年份开设相同的课程。我需要得到每所学校每年开设的课程总数。我通过以下查询做到了这一点。

SELECT s.school_id
     , s.name school_name
     , year(c.start_date) as the_year
     , COUNT(*) as total
  FROM course c 
  LEFT 
  JOIN school s 
    ON c.school_id = s.school_id
 group 
    by c.school_id
     , the_year 
 ORDER 
    BY the_year;

哪个工作正常，但是，我还需要获取每所学校最早到最新记录的年份的课程总数。我最终要打印的表格看起来像这样

School | 2018 | 2019 | 2020 | Total
-----------------------------------
ACME   | 0    | 2    | 0    | 2
Aca    | 2    | 0    | 1    | 3

除了 Total 列之外，我可以使用前面的查询创建此表。

示例输出

school_id | school_name | the_year | total 
------------------------------------------
3         | Aca         | 2018     | 2
7         | ACME        | 2019     | 2
7         | Aca         | 2020     | 1

如何查询数据库以获取总数的数据集，以便将其附加到输出表中？谢谢。

标签： mysqlsqldatetimecountpivot

解决方案

我认为你想要条件聚合：

select 
    s.name as school_name, 
    sum(year(c.start_date) = 2018) cnt_2018,
    sum(year(c.start_date) = 2019) cnt_2019,
    sum(year(c.start_date) = 2020) cnt_2020,
    count(*) as total
from course c 
inner join school s on c.school_id = s.school_id
group by s.school_id, s.name
order by s.name

笔记：

  • 您的原始查询对于start_date来自哪个表列的含义不明确；根据你的解释，我假设course

  • 我认为 aleft join不是真的需要，所以我将其更改为inner join

  • 用半开间隔表达条件表达式可能会更有效一些，例如：sum(c.start_date >= '2018-01-01' and c.start_date < '2019-01-01') cnt_2018

推荐阅读