如何创建带条件统计的汇总表？数据库多列状态统计

First, let's fix the issues with your function and then show the optimal approach to generate your desired statistics table.

Why Your Function Has Syntax Errors #

Your Countif function has several syntax issues:

• The RETURNS clause must specify a data type (e.g., INT), not a variable name.
• You need to declare a variable to store the count result.
• The IF statement requires a BEGIN...END block for multi-line logic, and you need to assign the count to a variable.
• The RETURN statement must return the variable value and should be placed inside the main BEGIN...END block.

A corrected scalar function (note: this is for single column-value checks, but we’ll show a more efficient approach below) would look like this:

CREATE FUNCTION dbo.Countif (@TableName NVARCHAR(100), @ColumnName NVARCHAR(100), @ValueCompare NVARCHAR(10))
RETURNS INT
AS
BEGIN
    DECLARE @Count INT
    DECLARE @SQL NVARCHAR(MAX)
    
    -- Use dynamic SQL to safely reference table/column names
    SET @SQL = N'SELECT @Count = COUNT(*) FROM ' + QUOTENAME(@TableName) + 
               N' WHERE ' + QUOTENAME(@ColumnName) + N' = @Value'
    
    EXEC sp_executesql @SQL, N'@Value NVARCHAR(10), @Count INT OUTPUT', 
                       @Value = @ValueCompare, @Count = @Count OUTPUT
    
    RETURN @Count
END

Optimal Approach: Conditional Aggregation to Generate the Stats Table #

Scalar functions can be slow for large datasets. A better way to build your exact desired table is using conditional aggregation, which handles all counts in a single query.

Step 1: Create the Statistics Table (if it doesn’t exist) #

First, set up the target table to store your stats:

CREATE TABLE StatusStatistics (
    Value NVARCHAR(10) PRIMARY KEY,
    x INT,
    y INT,
    z INT
)

Step 2: Populate the Table with Aggregated Counts #

Use CASE statements to calculate counts for each status, then insert the results into your stats table:

INSERT INTO StatusStatistics (Value, x, y, z)
SELECT 
    'pass' AS Value,
    COUNT(CASE WHEN x = 'pass' THEN 1 END) AS x,
    COUNT(CASE WHEN y = 'pass' THEN 1 END) AS y,
    COUNT(CASE WHEN z = 'pass' THEN 1 END) AS z
FROM YourSourceTable

UNION ALL

SELECT 
    'fail' AS Value,
    COUNT(CASE WHEN x = 'fail' THEN 1 END) AS x,
    COUNT(CASE WHEN y = 'fail' THEN 1 END) AS y,
    COUNT(CASE WHEN z = 'fail' THEN 1 END) AS z
FROM YourSourceTable

UNION ALL

SELECT 
    'blank' AS Value,
    COUNT(CASE WHEN x = 'blank' OR x IS NULL THEN 1 END) AS x, -- Include NULL if "blank" means empty/NULL
    COUNT(CASE WHEN y = 'blank' OR y IS NULL THEN 1 END) AS y,
    COUNT(CASE WHEN z = 'blank' OR z IS NULL THEN 1 END) AS z
FROM YourSourceTable

UNION ALL

SELECT 
    'total' AS Value,
    COUNT(x) AS x,
    COUNT(y) AS y,
    COUNT(z) AS z
FROM YourSourceTable

Replace YourSourceTable with the actual name of your table containing columns x, y, z.

Step 3: Refresh the Stats Table (for updates) #

If you need to refresh the statistics later, truncate the table first then re-run the insert query:

TRUNCATE TABLE StatusStatistics;
-- Re-run the INSERT query above

Key Notes #

• Conditional aggregation is far more efficient than calling a scalar function multiple times, especially for large datasets.
• Adjust the blank case logic if "blank" only refers to the string 'blank' (remove the OR x IS NULL part).
• For multiple source tables, modify the query to union results from each table or use joins as needed.

内容的提问来源于stack exchange，提问作者KAREN LORENA MARTINEZ MENDOZA