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   gt   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 a MS SQL 2005 environment

User · Answer

Not product-specific  but the SQL standard provides   SELECT COUNT   FILTER WHERE  lt condition-1 gt          COUNT   FILTER WHERE  lt condition-2 gt         FROM       for this purpose   Or something that closely resembles it  I don t know off the top of my hat   And of course vendors will prefer to stick with their proprietary solutions

User · Answer

SELECT COALESCE IF myColumn   1 COUNT DISTINCT NumberColumn  NULL  0  column1  COALESCE CASE WHEN myColumn   1 THEN COUNT DISTINCT NumberColumn  ELSE NULL END 0  AS column2 FROM AD CurrentView

User · Answer

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   So I used this     SELECT COL1  COL2      ETC         1   SELECT a vcount              FROM  SELECT vm2 visit id  count    AS vcount                    FROM dbo visitmanifests AS vm2                    WHERE vm2 inactive   0 AND vm2 visit id   vm Visit ID                    GROUP BY vm2 visit id  AS a   AS  No of Visits          COL xyz FROM etc etc   Of course you will need to format the result according to your display requirements

User · Answer

Why not like this   SELECT count 1  FROM AD CurrentView WHERE myColumn 1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Sql Server equivalent of a COUNTIF aggregate function

Examples related to sql

Examples related to sql-server-2005