{"id":3611,"date":"2024-04-01T12:09:04","date_gmt":"2024-04-01T05:09:04","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3611"},"modified":"2024-04-01T13:05:37","modified_gmt":"2024-04-01T06:05:37","slug":"sql-server-count-if","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-aggregate-functions\/sql-server-count-if\/","title":{"rendered":"SQL Server COUNT IF"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server COUNT IF<\/code> function to count values in a set based on conditions.<\/p>\n\n\n\n

How to form a SQL Server COUNT IF #<\/a><\/h2>\n\n\n\n

In SQL Server, the IIF<\/code> function<\/a> allows you to evaluate an expression and returns a value if the expression is true or another value if the expression is false. <\/p>\n\n\n\n

Here’s the syntax of the IFF function:<\/p>\n\n\n

IIF(expression, value_if_true, value_if_false)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To count values based on a specific condition, you can combine the COUNT<\/code> with the IIF<\/code> function:<\/p>\n\n\n
COUNT(IIF(expression, 1 , NULL))<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this expression, the COUNT<\/code> will calculate the total number of values when the expression is true. If the expression is false, the IIF function returns NULL<\/code>. Hence, the COUNT<\/code> function will not count it.<\/p>\n\n\n\n
SQL Server COUNT IF examples #<\/a><\/h2>\n\n\n\n
Let’s explore some examples of using the SQL Server COUNT<\/code> IF<\/code>.<\/p>\n\n\n\n
1) Setting up a sample table #<\/a><\/h3>\n\n\n\n
First, create a table<\/a> called employees<\/code> that stores the employee data:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> employees(\n  id<\/span> INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>, \n  full_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>, \n  employee_type varchar<\/span>(25<\/span>) not<\/span> null<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> employees (full_name, employee_type) \nVALUES<\/span> \n  ('John Doe'<\/span>, 'Full-time'<\/span>), \n  ('Jane Smith'<\/span>, 'Full-time'<\/span>), \n  ('Michael Johnson'<\/span>, 'Full-time'<\/span>), \n  ('Emily Brown'<\/span>, 'Full-time'<\/span>), \n  ('David Lee'<\/span>, 'Contractor'<\/span>), \n  ('Sarah Williams'<\/span>, 'Temp'<\/span>), \n  ('Matthew Taylor'<\/span>, 'Full-time'<\/span>), \n  ('Jessica Martinez'<\/span>, 'Contractor'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, retrieve data from the employees<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n  full_name, \n  employee_type \nFROM<\/span> \n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
full_name        | employee_type\n------------------------------<\/span>\nJohn Doe         | Full-time\nJane Smith       | Full-time\nMichael Johnson  | Full-time\nEmily Brown      | Full-time\nDavid Lee        | Contractor\nSarah Williams   | Temp\nMatthew Taylor   | Full-time\nJessica Martinez | Contractor\n(8 rows)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) SQL Server COUNT IF example #<\/a><\/h3>\n\n\n\n
The following statement uses the COUNT<\/code> with the IIF<\/code> function to get the total number of full-time employees:<\/p>\n\n\n
SELECT<\/span> COUNT<\/span>(IIF<\/span>(employee_type='Full-time'<\/span>,1<\/span>,NULL<\/span>)) full_time_employee_count\nFROM<\/span> employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
full_time_employee_count\n------------------------<\/span>\n5<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example uses the COUNT<\/code> with the IIF<\/code> function to get the total number of each employee type:<\/p>\n\n\n
SELECT<\/span> \n   COUNT<\/span>(IIF<\/span>(employee_type='Full-time'<\/span>,1<\/span>,NULL<\/span>)) full_time,\n   COUNT<\/span>(IIF<\/span>(employee_type='Contractor'<\/span>,1<\/span>,NULL<\/span>)) contractor,\n   COUNT<\/span>(IIF<\/span>(employee_type='Temp'<\/span>,1<\/span>,NULL<\/span>)) temp\nFROM<\/span> \n   employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
full_time | contractor | temp\n----------+------------+------<\/span>\n5         | 5          | 1\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n  employee_type, \n  COUNT<\/span>(*) employee_count \nFROM<\/span> \n  employees \nGROUP<\/span> BY<\/span> \n  employee_type;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
employee_type             employee_count\n------------------------- --------------<\/span>\nContractor                2\nFull-time                 5\nTemp                      1\n\n(3 rows affected)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the COUNT<\/code> with the IIF<\/code> function to obtain the total number of rows based on a condition.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Note that if you use the GROUP BY<\/code><\/a> clause, you’ll get the rows arranged vertically:<\/p>\n\n\n
Second, insert some rows<\/a> into the employees<\/code> table:<\/p>\n\n\n
The COUNT<\/a><\/code> function is an aggregate function<\/a> that allows you to calculate the total number of values in a set. The COUNT<\/code> function ignores NULL<\/code> when counting.<\/p>\n\n\n\n