How to Count Records Meeting a Specific Condition and Calculate Percentage in SQL Server?

Conditional counting and WHERE clause in SQL Server

In SQL Server, you can use the COUNT() aggregate function combined with the WHERE clause to count the number of records that meet specific conditions.

The goal of the following example is to count the number of records where the MyColumn value is equal to 1. Here is the modified query:

SELECT  UID, 
        COUNT(UID) AS TotalRecords, 
        SUM(ContractDollars) AS ContractDollars,
        (SELECT COUNT(*) 
         FROM dbo.AD_CurrentView AS Sub
         WHERE Sub.UID = Outer.UID AND Sub.MyColumn = 1) * 100.0 / COUNT(UID) AS PercentageOfOne
FROM    dbo.AD_CurrentView AS Outer
GROUP BY UID
HAVING  SUM(ContractDollars) >= 500000

The following is a breakdown of the query steps:

1. Create a subquery to count the number of records in each UID group that meet the condition (MyColumn = 1).
2. Use UID columns to join the main query with the subquery.
3. Calculate the percentage of records where MyColumn is equal to 1 by dividing the count in the subquery by the total number of records for each UID. Note that the divisor 100 is changed to 100.0 to ensure floating point division and get a more accurate percentage result.
4. Apply the calculated percentage to the PercentageOfOne column.

This revised query avoids potential integer division issues by using 100.0 instead of 100 in the percentage calculation, ensuring a more accurate floating-point result.

The above is the detailed content of How to Count Records Meeting a Specific Condition and Calculate Percentage in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!