如何在 SQL SERVER 查询中显示零计数 [英] How to Show Zero Count in SQL SERVER Query

问题描述

我想知道我们如何显示零计数?我只有一张表，我正在做每月计数，但有些情况下 1 个月没有任何行，SQL Server 在计数期间跳过该月，但我需要在我的报告中显示.

I want to know that how we can display ZERO count? I have only one table and I am doing monthly count but some how 1 month doesn't have any rows and SQL Server is skipping that month during count but I need to show in my report.

这是我正在使用的查询:

This is my query that I am using:

SELECT 
    MONTH(createdDate) [Month], 
    ISNULL(COUNT(id), 0) [Count]
FROM 
    [PocketLife].[dbo].[Notifications]
WHERE 
    description = 'Welcome to Pocket Link' AND 
    YEAR(CAST(createdDate as DATE)) = YEAR(GETDATE())
GROUP BY 
    MONTH(createdDate)

目前上面的查询是这样显示的，但它缺少零的第一个月记录.

Currently the above query is showing like this but it is missing the First Month Record which is ZERO.

Month   Count
--------------
2            5 
3          295 
4         8295 
5       149855 
6       447752 
7         6311 

但它应该如下所示，这是实际的结果:

But it should Show as below and this is the actual Result:

Month   Count
--------------
1            0 
2            5 
3          295 
4         8295 
5       149855 
6       447752 
7         6311 

任何帮助将不胜感激.

推荐答案

您可以使用范围从 1 到 12 的 Tally Table 作为月份列表，然后对您的查询执行 LEFT JOIN.为 N 添加一个额外的过滤器，以便它只返回截至当前月份的记录.

You can use a Tally Table ranging from 1 - 12 for the list of months, then do a LEFT JOIN on your query. Add an additional filter for N so that it only returns records up to the current month.

用您的原始查询替换 Cte 的内容.

WITH Cte([Month], [Count]) AS(
    SELECT 2, 5 UNION ALL
    SELECT 3, 295 UNION ALL
    SELECT 4, 8295 UNION ALL
    SELECT 5, 149855 UNION ALL
    SELECT 6, 447752 UNION ALL
    SELECT 7, 6311
),
CteTally(N) AS(
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL 
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT
    t.N AS [Month],
    ISNULL(c.Count, 0) AS [Count]
FROM CteTally t
LEFT JOIN Cte c
    ON t.N =c.Month
WHERE
    t.N <= MONTH(GETDATE())

这篇关于如何在 SQL SERVER 查询中显示零计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！