SQL QUERY在使用COUNT时显示NULL值

I need to produce the number of healthclub members that are enrolled through their employer(several different employers not just 1) for a monthly membership and what level of membership they have plus their family members. The problem I am having is that currently we do not have any LEVEL D membership but may in the future. I need the report to display ‘0’ when there is no membership. I tried

我需要通过他们的雇主（几个不同的雇主，而不仅仅是1个）注册的健康俱乐部会员数量，每月会员资格以及他们的会员资格加上他们的家庭成员。我遇到的问题是，目前我们没有任何LEVEL D会员资格，但可能在将来。当没有会员资格时，我需要报告显示“0”。我试过了

COUNT(DISTINCT CUSTOMER_ID) + 
  COUNT(CASE WHEN CUSTOMER_ID IS NULL THEN 1 END) AS NUMBER_OF_CUSTOMERS

And it did not work, any help is appreciated.

它没有用，任何帮助都表示赞赏。

SELECT
    MEMBERSHIP_TYPE,
    COUNT(DISTINCT CUSTOMER_ID) AS NUMBER_OF_CUSTOMERS,
    COUNT(CASE WHEN CUSTOMER_RELATION = ‘FAMILYMEMBER’ THEN 1 END) AS FAMILY_MEMBERS,
    COUNT(DISTINCT CUSTOMER_ID) + COUNT(CASE WHEN CUSTOMER_RELATION    ‘FAMILY_MEMBERS’ THEN 1 END) AS TOTAL

This is what I currently get

这就是我目前得到的

MEMBERSHIP_TYPE  NUMBER_OF_CUSTOMERS    FAMILY_MEMBERS  TOTAL
-------------------------------------------------------------
LEVEL A                 100                  25           125
LEVEL B                 630                 340           970
LEVEL C                1201                 630          1831

I need this

我需要这个

MEMBERSHIP_TYPE  NUMBER_OF_CUSTOMERS    FAMILY_MEMBERS  TOTAL
-------------------------------------------------------------
LEVEL A                 100                  25           125
LEVEL B                 630                 340           970
LEVEL C                1201                 630          1831
LEVEL D                   0                   0             0

1 个解决方案

#1

You will hardcode the value into the select statement. Add a UNION ALL to the SQL statement

您将该值硬编码到select语句中。将UNION ALL添加到SQL语句中

..your current select statement
UNION
SELECT LEVEL D, 0,0,0;

When an entry of Level D will be made in the table, the updated value will be shown correctly.

当在表格中输入D级条目时，更新的值将正确显示。

#1