Excels Countif in SQL

The Microsoft Excel function countif counts cells that satisfy a condition:

The same behavior can be obtained in SQL by using a case expression inside the count function:

In Excel, the <source> defines arbitrary cells—Ax:Ay in the following examples. In SQL, the picking the rows is separate from the picking of the columns. The the group by and over clauses specify the rows. The column is explicitly used in the <condition> that is put into the case expression.

The condition is not put under quotes—not even when using a comparison operator:

Text values, however, must be put under single quotes0:

Whether or not SQL text comparisons ignore case differences depends on the so-called collation. Even the default varies between database products: MySQL, MariaDB and SQL Server perform case-insensitive comparisons by default. PostgreSQL, the Oracle database and SQLite do—per default—take case differences into account.

Unlike the Excel countif function, SQL does not apply wildcard matches when comparing strings with the equals sign ( = ). To use wildcards in SQL, you have to use the like operator. The like operator uses underscore ( _ ) as a wildcard for a single character and the percent sign ( % ) as the wildcard for any number of characters—like ? and * in Excels countif .

Countif over multiple columns is done as the sum of one count function per column:

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. The following expression counts the rows that have neither the null value or the empty string.

Note that the SQL equals operator ( = ) cannot be used to check for the null value—you have to use is [not] null instead.

Смотрите также

Впервые познакомился с WordPress в 2009 году. Организатор и спикер на конференциях WordCamp. Преподаватель в школе Нетология.

Если вам нужна помощь с сайтом или разработка с нуля на WordPress / WooCommerce — пишите. Я и моя команда будем рады вам помочь!

Combining SQL Server catalog views

We can use SQL Server catalog views with the following dynamic management views:

sys.tables – populates the list of tables.
sys.indexes – populates the list of indexes of the table.
sys.partitions – populates the rows of each partition.

Читайте так же:

Brother 7025 сброс счетчика

To get the count of rows, run the following script:

Output:

The query populates the table name, index name, and total rows in all partitions.

Now, let us review the IO Statistics:

As you can see, the query performs only 30 logical reads.

Pros:

This approach is faster than the COUNT function. It does not acquire a lock on the user table, so you can use it in a busy system.

Cons:

The method populates an approximate Count of rows. In the Microsoft documentation of sys.partitions, you can see that the rows column brings the approximate number of rows for the partitions.

Thus, if you are looking for a query that brings the result faster than the COUNT function, you can use this one. However, the result might be inaccurate.

SQL COUNT function examples

Let’s take some examples to see how the COUNT function works. We will use the employees table in the sample database for the demonstration purposes.

SQL COUNT(*) example

To get the number of rows in the employees table, you use the COUNT(*) function table as follows:

To find how many employees who work in the department id 6, you add the WHERE clause to the query as follows:

Similarly, to query the number of employees whose job id is 9, you use the following statement:

SQL COUNT with GROUP BY clause example

To find the number of employees per department, you use the COUNT with GROUP BY clause as follows:

To get the department name in the result set, we need to use the inner join to join the employees table with the departments table as follows:

SQL COUNT(*) with ORDER BY clause example

You can use the COUNT(*) function in the ORDER BY clause to sort the number of rows per group. For example, the following statement gets the number of employees for each department and sorts the result set based on the number of employees in descending order.

SQL COUNT with HAVING clause example

To filter the groups by the result of the COUNT(*) function, we need to use the COUNT(*) function in the HAVING clause.

For example, the following statement gets the departments and their number of employees. In addition, it selects only departments whose the number of employees is greater than 5.

SQL COUNT(DISTINCT expression) example

To get the number of jobs in the employees table, you apply the COUNT function to the job_id column as the following statement:

The query returns 40 that includes the duplicate job id. We expected to find the number of jobs that are holding by employees.

To remove the duplicate, we add the DISTINCT keyword to the COUNT function as follows:

Читайте так же:

Обратный счетчик для сайта joomla

You can use the COUNT DISTINCT to get the number of managers as the following query:

Note that the president does not have the manager.

In this tutorial, you have learned the various ways to apply the SQL COUNT function to get the number of rows in a group.

MySQL COUNT() function illustration

Setting up a sample table

First, create a table called count_demos :

Second, insert some rows into the count_demos table:

Third, query data from the count_demos table:

MySQL COUNT(*) example

The following statement uses the COUNT(*) function to return all rows from the count_demos table:

This example uses the COUNT(*) function with a WHERE clause to specify a condition to count only rows whose value in the column val is 2:

MySQL COUNT(expression) example

If you specify the val column in the COUNT() function, the COUNT() function will count only rows with non-NULL values in the val column:

Notice that two NULL values are not included in the result.

MySQL COUNT(DISTINCT expression) example

This example uses COUNT(DISTINCT expression) to count non-NULL and distinct values in the column val :

How to count the number of rows in a table in SQL Server

It seems like such an innocent request. It isn’t too hard to get this information out of SQL Server. But before you open SSMS and whip out a quick query, understand that there are multiple methods to get this information out of SQL Server – and none of them are perfect!

COUNT(*) or COUNT(1)

The seemingly obvious way to get the count of rows from the table is to use the COUNT function. There are two common ways to do this – COUNT(*) and COUNT(1). Let’s look at COUNT(*) first.

The STATISTICS IO output of this query shows that SQL Server is doing a lot of work! Over 100,000 logical reads, physical reads, and even read-ahead reads need to be done to satisfy this query.

Looking at the execution plan, we can see an Index Scan returning over 31 million rows. This means that SQL Server is reading every row in the index, then aggregating and counting the value – finally ending up with our result set. The cost of this query? 123.910000.

count 1

The query results: 31,263,601 rows.

count 2

Now, let’s look at the behavior of COUNT(1).

We can see from STATISTICS IO that we have a large number of logical reads – over 100,000.

The execution plan again shows an index scan returning over 31 million rows for processing. The query cost is the same, 123.910000.

count 3

The results here are the same – 31,263,601 rows.

count 4

The benefit of using COUNT is that it is an accurate indicator of exactly how many rows exist in the table at the time query processing begins. However, as the table is scanned, locks are being held. This means that other queries that need to access this table have to wait in line. This might be acceptable on an occasional basis, but I frequently see applications issuing these types of queries hundreds or thousands of times per minute.

Читайте так же:

Счетчик просмотра новости dle

sys.tables + sys.indexes + sys.partitions

We can join several SQL Server catalog views to count the rows in a table or index, also. sys.tables will return objects that are user-defined tables; sys.indexes returns a row for each index of the table; and sys.partitions returns a row for each partition in the table or index. I am going to query for the table ID, name, and count of rows in all partitions.

The output of STATISTICS IO here shows far fewer reads – 15 logical reads total.

The execution plan is more complex, but much less work – the query cost here is 0.0341384.

count 5

The results of the query are also the same – 31,263,301.

The benefits of using this method are that the query is much more efficient, and it doesn’t lock the table you need the count of rows for.

However, you need to be cautious when counting the rows on a table that is frequently inserted into or deleted from. The TechNet documentation for sys.partitions.rows says it indicates the “approximate number of rows for this partition”. How approximate? That information isn’t documented. Understand, though, that if you use this method, you potentially sacrifice up-to-the-moment accuracy for performance.

sys.dm_db_partition_stats

A third option is to use the dynamic management view sys.dm_db_partition_stats. This returns one row per partition for an index.

The STATISTICS IO output of this query is even lower – this time, only two logical reads are performed.

The execution plan is less complex than our second example involving the three system views. This query also has a lower cost – 0.0146517.

count 7

The query results are the same as the previous examples – 31,263,301 rows.

count 8

Using this DMV has the same benefits as the system views – fewer logical reads and no locking of the target table. The query is also simpler to write, involving only one object.

But again, the TechNet documentation for sys.dm_db_partition_stats.row_count says it is “the approximate number of rows in the partition”, and when this information is updated is not documented. Here, you are also potentially sacrificing accuracy for performance.

Time to do some digging

The questions that you need to work with the business to answer are, “How up-to-date must the row count be? What is the business purpose? How often do you insert into or delete from that table, and how often do you count the rows?” If the accuracy of the row count is crucial, work to reduce the amount of updates done to the table. If performance is more important, and the row count could be approximate, use one of the system views.

Читайте так же:

Счетчик страниц panasonic mb1500

Wanna learn more tricks for free?

Check out our free T-SQL Level Up online class – we guarantee it’s the best T-SQL training trailer you’ve ever seen:

VBA Count

В Excel мы используем функцию count для подсчета количества ячеек, которые содержат числа. То же самое можно сделать и в VBA. В VBA мы можем использовать ту же функцию Count, чтобы узнать, сколько номеров ячеек содержат числа. Считает только ячейку с числами. Значения, отличные от чисел, не могут быть подсчитаны.

Синтаксис Count в Excel VBA

Синтаксис для функции VBA Count в Excel выглядит следующим образом:

Как использовать VBA Count в Excel?

Мы узнаем, как использовать функцию подсчета VBA, с несколькими примерами в Excel.

Вы можете скачать этот шаблон VBA Count Excel здесь — Шаблон VBA Count Excel

Пример № 1 — количество VBA

Для реализации этого у нас есть список некоторых данных в столбце А. Этот список содержит цифры и тексты, как показано ниже. Теперь мы с помощью функции Count в VBA увидим, сколько ячеек имеют числа. Для этого мы определили ячейку в позиции A8, где мы увидим вывод функции Count через VBA.

Шаг 1: Для этого нам нужен модуль. Перейдите на вкладку меню «Вставка» и выберите «Модуль», как показано ниже из списка.

Шаг 2: После этого мы получим пустое окно модуля. Теперь в это напишите подкатегорию VBA Count. Или выберите любое другое имя согласно вашему выбору.

Код:

Шаг 3: Выберите диапазон ячейки, в которой мы хотим применить функцию Count. Здесь нашей выходной ячейкой является A8, как определено выше. Таким образом, мы выбрали его в качестве нашего диапазона .

Код:

Шаг 4: Теперь получите команду Value, и это позволит нам добавить значение в нее.

Код:

Шаг 5: Теперь с помощью функции подсчета выберите диапазон ячеек, из которого мы хотим получить количество ячеек, которое содержит только числа. Здесь мы выбрали диапазон ячеек от А1 до А6.

Код:

Ste 6: После этого скомпилируйте код и запустите, нажав кнопку воспроизведения. Как мы видим ниже, количество ячеек, содержащих числа, равно 3. Что означает, что функция Count в VBA дала счетчик ячеек с номерами от A1 до A3.

Пример № 2 — Количество VBA

Аналогичным образом, у нас есть другой набор данных. Но эти данные имеют некоторые даты, число с текстом вместе с цифрами и текст, как показано ниже. Мы исправили ячейку C12, где мы увидим вывод функции Count через VBA.

Теперь мы применим функцию Count и посмотрим, может ли она считать даты и числовые текстовые ячейки или нет. Мы можем снова написать новый код или ссылаться на тот же код, который мы видели в примере 1, и просто изменить ячейки ссылки.

Шаг 1: Перейдите на вкладку меню «Вставка» и выберите пункт «Модуль», как показано ниже в списке.

Читайте так же:

Код счетчика дней до лета

Код:

Шаг 2: Выберите диапазон ячеек, в котором мы хотим увидеть вывод. Вот эта ячейка C12.

Код:

Шаг 3: Теперь используйте функцию подсчета в кавычках для выбора диапазона тех ячеек, которые нам нужно посчитать. Здесь этот диапазон от ячейки C1 до C10.

Код:

Шаг 4: Теперь запустите приведенный выше код.

Мы увидим, что функция Count вернула счетчик 6, как показано ниже. Это означает, что функция count может также считать ячейки с датой. Здесь значения, выделенные жирным шрифтом, являются значениями, которые только что были подсчитаны с помощью функции Count в VBA.

Пример № 3 — Количество VBA

Есть еще один способ использовать функцию подсчета в VBA. Этот метод предполагает использование активных ячеек листа. Здесь мы будем использовать те же данные, которые мы видели в примере-1.

Шаг 1: Откройте новый модуль и создайте подкатегорию с именем VBA Count, как показано ниже.

Код:

Шаг 2. Сначала вставьте функцию ActiveCell в VBA. Это поможет в выборе диапазона ячеек.

Код:

Шаг 3: Теперь с помощью функции Formula выберите номер строки и номер столбца, которые мы хотим вставить в функцию Count. Здесь наша ссылочная строка начинается с 1, а столбец также равен 1.

Код:

Шаг 4: Теперь вставьте функцию Count под кавычками, как показано ниже.

Код:

Шаг 5: Выберите диапазон ячеек от точки, где мы применяем функцию Count. По мере того, как мы поднимаемся от A8 к A1, количество строк будет равно « -7 », а столбец будет первым, но ничто не будет упомянуто в числе строк «-2» от начальной точки, которая является ячейкой A8 .

Код:

Шаг 6: Теперь выберите диапазон ячеек, в котором мы хотим увидеть результат. Здесь, в этой ячейке диапазона A8, мы также увидим курсор.

Код:

Шаг 7: Теперь запустите код. Мы увидим, что функция count вернула то же число, что и 3, которое мы получили в примере-1.

Плюсы VBA Count

Это так же просто, как применять функцию подсчета в Excel.
Это одна из самых простых функций, которые можно автоматизировать с помощью VBA.
Если процесс Count повторяется несколько раз, то его автоматизация с помощью функции Count в VBA — это экономия времени и усилий.

То, что нужно запомнить

При применении функции подсчета в VBA всегда указывайте имя функции в кавычках.
Поскольку мы используем Count в Excel, то же самое можно увидеть и при применении Count Function в VBA.
Процесс применения VBA Count также может быть выполнен путем перекодирования макроса.
Всегда сохраняйте написанный код в VBA в Macro, включите формат файла Excel, чтобы избежать потери кода.

Excels Countif in SQL