查询以选择每年的记录数 [英] query to select count of records for each year

问题描述

抱歉这个新手问题......我试图获得我的表每年的行数......这是我的尝试:

sorry for this newbie question ... im trying to get number of rows per year of my table..... this is my try:

select count(*)
from tbl_fact
where stat = 1 and id = 16
group by (year(fact_date))
go

和

select count(*)
from tbl_fact
where stat = 1 and id = 16 and year(fact_date) in (select Distinct(year(fact_date)) from tbl_fact)
group by (year(fact_date))
go

我有标记日期的记录，现在我有 2017 年和 2018 年的日期，所以我需要每年计数.但是 id=16 只有 2018 年而不是 2017 年的日期标签，所以我得到的结果为

i have records that taged with dates that for now i have dates from 2017 and 2018 so i need counts for each year. but the id=16 has only date tag of 2018 not 2017 so i get result as

eg: 15

它应该是什么样子

eg: 0 //2017
    15 //2018

推荐答案

一个简单的方法来获取数据中的所有年份——即使它们不满足 where 子句的条件——- 是使用条件聚合:

A simple method to get all years in the data -- even when they don't meet the conditions of the where clause -- is to use conditional aggregation:

select year(fact_date) as yyyy,
       sum(case when stat = 1 and id = 16 then 1 else 0 end) as cnt_16
from tbl_fact
group by year(fact_date)
order by yyyy;

这篇关于查询以选择每年的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！