MySQL统计总行数及含NULL值行数问题求助

Hey there! I totally get the confusion with COUNT sometimes—its behavior with NULL values can trip up even experienced folks. Let's work through your problem step by step.

1. 统计数据表总行数 #

The common mistake here is using COUNT(your_rating_column) instead of COUNT(*) or COUNT(1). Here's why:

• COUNT(列名) ignores all NULL values in that column, so it only counts rows where the column has a non-NULL value.
• COUNT(*) and COUNT(1) count every single row in the table, regardless of whether any columns have NULLs.

Use this query to get the total number of rows:

SELECT COUNT(*) AS total_rows FROM your_table_name;

2. 统计目标字段含NULL值的行数 #

To count how many rows have a NULL in your specific rating column, you have two reliable options:

Option 1: Filter with WHERE #

This is the most straightforward approach—just filter for rows where the column is NULL, then count them:

SELECT COUNT(*) AS null_rating_rows FROM your_table_name WHERE your_rating_column IS NULL;

Option 2: Combine total and NULL counts in one query #

If you want both numbers in a single result (super handy!), use a CASE statement inside SUM:

SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN your_rating_column IS NULL THEN 1 ELSE 0 END) AS null_rating_rows
FROM your_table_name;

This works because the CASE returns 1 for every NULL row and 0 otherwise, then SUM adds those up to get the total NULL count.

Bonus: Quick tip for your rating categories #

Since you mentioned the rating values map to 1=差（Bad）, 2=好（Good）, 3=很好（Very Good）, here's a query to count each category (including NULLs) if you ever need it:

SELECT
  CASE your_rating_column
    WHEN 1 THEN '差（Bad）'
    WHEN 2 THEN '好（Good）'
    WHEN 3 THEN '很好（Very Good）'
    ELSE '未评级（NULL）'
  END AS rating_category,
  COUNT(*) AS category_count
FROM your_table_name
GROUP BY rating_category;

内容的提问来源于stack exchange，提问作者Augusto Coelho Henriques