Sql Server equivalent of a COUNTIF aggregate function

Question

I'm building a query with a GROUP BY clause that needs the ability to count records based only on a certain condition (e.g. count only records where a certain column value is equal to 1).

SELECT  UID, 

        COUNT(UID) AS TotalRecords, 

        SUM(ContractDollars) AS ContractDollars,

        (COUNTIF(MyColumn, 1) / COUNT(UID) * 100) -- Get the average of all records that are 1

FROM    dbo.AD_CurrentView

GROUP BY UID

HAVING  SUM(ContractDollars) >= 500000

The COUNTIF() line obviously fails since there is no native SQL function called COUNTIF, but the idea here is to determine the percentage of all rows that have the value '1' for MyColumn.

Any thoughts on how to properly implement this in an MS SQL 2005 environment?

Answer 1

You can use the SUM (not COUNT!) combined with the CASE statement, this way:

SELECT SUM(CASE WHEN myColumn=1 THEN 1 ELSE 0 END)

FROM AD_CurrentView

Note: NULLs can be environment-dependent. 

You can handle nulls like this:

SELECT SUM(CASE WHEN ISNULL(myColumn,0)=1 THEN 1 ELSE 0 END)

FROM AD_CurrentView

Learn SQL commands with the help of these  SQL Commands Cheat Sheet.

To master SQL statements, queries and become proficient in SQL queries, enroll in an industry-recognized SQL Online Course.

Comments

This helped solve my issue

---

Worked for me.

---

This resolved my issue perfectly.

Answer 2

I usually do what @Soni Kumari recommended, but brainstormed and tested a slightly hokey alternative that I felt like sharing.

You can take advantage of the fact that COUNT(ColumnName) doesn't count NULLs, and use something like this:

SELECT COUNT(NULLIF(0, myColumn))

FROM AD_CurrentView

NULLIF - returns NULL if the two passed in values are equivalent.

Advantage: Shows your intent to COUNT rows instead of having the SUM() notation.

Disadvantage: Not as explicit how it is working ("magic" is usually bad).

Comments

It worked for me!

---

I had to use COUNTIF() in my case as part of my SELECT columns AND to mimic a % of the number of times each item appeared in my results.

---

Both the answers of @soni and @vinita were helpful and solved the issue.

Answer 3

I would use the following syntax because with this the advantage is that it is  ANSI complient and not tied to a particular database vendor.

select count(case when myColumn = 1 then 1 else null end)
from   AD_CurrentView

 

Comments

This answer is best because it helps to get the idea of "counting rows"  and is more extensible since you can use any comparison operator; not just =. I'm using it for "count the number of responses >=2".

---

It worked for me.
Thanks!

---

Nice explanation.

Answer 4

So, for this particular scenario, you can use “SUM” with the conditional statement instead of countif.

So, below are commands/queries for doing that.

SELECT  UID, 

        COUNT(UID) AS TotalRecords, 

        SUM(ContractDollars) AS ContractDollars,

        (SUM(CASE WHEN MyColumn = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(UID)) AS PercentageRecords

FROM    dbo.AD_CurrentView

GROUP BY UID

HAVING  SUM(ContractDollars) >= 500000;

So, in these commands, “ SUM(CASE WHEN MyCloumn = 1 THEN 1 ELSE 0 END) “ is finding out by counting only rows.

Multiplying with 100.0 ensures that the result is in percentage with decimal points.

And “COUNT” tells us the number of rows that meet the condition in the question.

Answer 5

In SQL you can obtain the desired output by using SUM() function along with conditional expressions such as a CASE statement.
SELECT
    SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS Count_If_Result
FROM
    Table_name;
Lets take an example:
SELECT
    SUM(CASE WHEN Status = 'Completed THEN 1 ELSE 0 END) AS CompletedCount
FROM
    Sales;