Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sql Server equivalent of a COUNTIF aggregate function

People also ask

Does SQL have a Countif function?

The function Countifs can often be implemented with an and condition in the case expression. The function counta can be implemented with a case expression as well. For that, SQL makes a distinction between empty strings and the null value.

How do I count aggregate functions in SQL?

Returns as a BIGINT the number of rows in each group where the expression is not NULL . If the query has no GROUP BY clause, COUNT returns the number of table rows. The COUNT aggregate function differs from the COUNT analytic function, which returns the number over a group of rows within a window.

Is count a valid aggregate function in SQL?

The following are the most commonly used SQL aggregate functions: AVG – calculates the average of a set of values. COUNT – counts rows in a specified table or view. MIN – gets the minimum value in a set of values.

Can I use count in case statement in SQL?

CASE can be used in conjunction with SUM to return a count of only those items matching a pre-defined condition. (This is similar to COUNTIF in Excel.) The trick is to return binary results indicating matches, so the "1"s returned for matching entries can be summed for a count of the total number of matches.


You could use a SUM (not COUNT!) combined with a CASE statement, like this:

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

Note: in my own test NULLs were not an issue, though this can be environment dependent. You could handle nulls such as:

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

I usually do what Josh 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 the same.

Advantage: Expresses your intent to COUNT rows instead of having the SUM() notation. Disadvantage: Not as clear how it is working ("magic" is usually bad).


I would use this syntax. It achives the same as Josh and Chris's suggestions, but with the advantage 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

How about

SELECT id, COUNT(IF status=42 THEN 1 ENDIF) AS cnt
FROM table
GROUP BY table

Shorter than CASE :)

Works because COUNT() doesn't count null values, and IF/CASE return null when condition is not met and there is no ELSE.

I think it's better than using SUM().


Adding on to Josh's answer,

SELECT COUNT(CASE WHEN myColumn=1 THEN AD_CurrentView.PrimaryKeyColumn ELSE NULL END)
FROM AD_CurrentView

Worked well for me (in SQL Server 2012) without changing the 'count' to a 'sum' and the same logic is portable to other 'conditional aggregates'. E.g., summing based on a condition:

SELECT SUM(CASE WHEN myColumn=1 THEN AD_CurrentView.NumberColumn ELSE 0 END)
FROM AD_CurrentView

Why not like this?

SELECT count(1)
FROM AD_CurrentView
WHERE myColumn=1