{"id":3813,"date":"2023-11-01T12:55:36","date_gmt":"2023-11-01T12:55:36","guid":{"rendered":"https:\/\/www.copahost.com\/blog\/?p=3813"},"modified":"2023-11-19T12:49:33","modified_gmt":"2023-11-19T12:49:33","slug":"count-sql","status":"publish","type":"post","link":"https:\/\/www.copahost.com\/blog\/count-sql\/","title":{"rendered":"COUNT SQL: how to count records in SQL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The COUNT command <strong>allows you to count the number of records that satisfy a specific condition<\/strong>&nbsp;in a search.&nbsp;COUNT&nbsp;<strong>SQL<\/strong>&nbsp;is widely used in various contexts. From sales and stock analyzing to the evaluation of systems and processes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In this article, we will explore COUNT, addressing its forms of use. Also, characteristics and applications in different sectors and contexts.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Mostly any database supports COUNT in SQL.&nbsp;<a href=\"https:\/\/www.copahost.com\/en\/\" target=\"_blank\" rel=\"noreferrer noopener\">For example, in the Copahost website hosting<\/a>&nbsp;MySQL database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><div id=\"ez-toc-container\" class=\"ez-toc-v2_0_69_1 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#When_is_COUNT_SQL_utilized\" title=\"When is COUNT SQL utilized?\">When is COUNT SQL utilized?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#COUNT_variants\" title=\"COUNT variants\">COUNT variants<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#Data_aggregation_using_COUNT_SQL\" title=\"Data aggregation using COUNT SQL\">Data aggregation using COUNT SQL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#How_is_COUNT_SQL_related_to_data_aggregations\" title=\"How is COUNT SQL related to data aggregations?\">How is COUNT SQL related to data aggregations?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#Usage_examples_in_data_aggregations\" title=\"Usage examples in data aggregations\">Usage examples in data aggregations<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#Examples_using_COUNT_in_conjunction_with_other_functions\" title=\"Examples using COUNT in conjunction with other functions\">Examples using COUNT in conjunction with other functions<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#1_Count_the_number_of_sales_by_state\" title=\"1. Count the number of sales by state\">1. Count the number of sales by state<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#2_Count_the_number_of_sales_by_product_category\" title=\"2. Count the number of sales by product category\">2. Count the number of sales by product category<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#3_Count_the_number_of_sales_per_time_period\" title=\"3. Count the number of sales per time period\">3. Count the number of sales per time period<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#4_Finding_the_companys_oldest_customer\" title=\"4. Finding the company&#8217;s oldest customer\">4. Finding the company&#8217;s oldest customer<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/#5_Finding_the_customer_who_made_the_highest_value_sale\" title=\"5. Finding the customer who made the highest value sale\">5. Finding the customer who made the highest value sale<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"When_is_COUNT_SQL_utilized\"><\/span>When is COUNT SQL utilized?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Various situations and contexts uses COUNT in SQL. Its main purpose is to count the number of records that meet a certain condition or criteria.&nbsp;Let\u2019s list some useful examples of using COUNT:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Record count:<\/strong>&nbsp;we should use to determine the number of records in a table. Without importing the selected columns.&nbsp;For example, we can apply COUNT(*) to count all records in a sales table.<\/li>\n\n\n\n<li><strong>Counting records with certain characteristics:<\/strong>&nbsp;We can combine COUNT with other SQL statements, such as&nbsp;<a href=\"https:\/\/www.copahost.com\/blog\/update-sql\/\" target=\"_blank\" rel=\"noreferrer noopener\">UPDATE<\/a>&nbsp;,&nbsp;<a href=\"https:\/\/www.copahost.com\/blog\/join-sql\/\" target=\"_blank\" rel=\"noreferrer noopener\">JOIN<\/a>&nbsp;and WHERE, to count only records that meet a criteria.&nbsp;For example, we can use COUNT(*) with WHERE to count only records with a specific value in a column.<\/li>\n\n\n\n<li><strong>Count of unique values:<\/strong>&nbsp;We can also use COUNT to count the number of unique values \u200b\u200bin a column of a table.&nbsp;For example, we use COUNT(DISTINCT column) to count the number of unique values \u200b\u200bin a column of a table.<\/li>\n\n\n\n<li><strong>Data analysis:<\/strong>&nbsp;We use COUNT extensively in data analyzing to identify patterns, trends and problems in systems and processes.&nbsp;For example, to analyze the frequency of problems in a system. Or even to identify the best-selling items in a store.<\/li>\n\n\n\n<li><strong>Stock and stock tracking<\/strong>&nbsp;: We use COUNT SQL to control the number of items in stock or inventory.&nbsp;For example, we use COUNT to count the number of items in stock at a given time.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"COUNT_variants\"><\/span>COUNT variants<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">It&nbsp;<code>COUNT SQL<\/code>has several variants that we can use to obtain different results and analyze data more precisely.&nbsp;Let&#8217;s explore the main variants of COUNT SQL:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><code><strong>COUNT()<\/strong><\/code><em>\u2013<\/em>&nbsp;The most basic form of COUNT&nbsp;<em>.&nbsp;<\/em>We use COUNT() to count all records in a table, without importing the selected columns.&nbsp;For example, we can count the total number of records in the table&nbsp;&nbsp;employees.&nbsp;See below:<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT COUNT(*) FROM employees;<\/code><\/span><\/pre>\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><code><strong>COUNT(column)<\/strong><\/code>\u2013 We know that we use the variant&nbsp;<code>COUNT<\/code>to count the number of records with a specific value in a table column.&nbsp;In this sense,&nbsp;<code>COUNT(column)<\/code> only takes into account records for which the specified column is not null.&nbsp;For example, we can count the number of records in the table&nbsp;&nbsp;<code>employees<\/code>&nbsp;that have a salary value greater than 50000. See below:<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT COUNT(salary) FROM employees WHERE salary &gt; 50000;<\/code><\/span><\/pre>\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li><code><strong>COUNT(expression)<\/strong><\/code>\u2013 We use it&nbsp;COUNT(expression) to count the number of records that satisfy a specific condition.&nbsp;Thus, the expression can include multiple clauses, such as WHERE, AND, OR, and NOT.&nbsp;For example, we can count the number of records in the table&nbsp;&nbsp;<code>employees<\/code>&nbsp;that have a sales department and a salary greater than 40000.<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT COUNT(*) FROM employees WHERE department = <span class=\"hljs-string\">'Sales'<\/span> AND salary &gt; <span class=\"hljs-number\">40000<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li><code><strong>COUNT(DISTINCT column)<\/strong><\/code>\u2013 We apply the variant&nbsp;<code>COUNT<\/code>to count the number of unique values \u200b\u200bin a column of a table.&nbsp;This way,&nbsp;<code>COUNT(DISTINCT column)<\/code>only takes into account unique values \u200b\u200bin a column, excluding duplicate values.&nbsp;For example, we can count the number of unique values \u200b\u200bin the&nbsp;&nbsp;<code>employee_id<\/code>&nbsp;table&nbsp; column&nbsp;<code>sales<\/code>.&nbsp;See below:<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT COUNT(DISTINCT employee_id) FROM sales;<\/code><\/span><\/pre>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Data_aggregation_using_COUNT_SQL\"><\/span>Data aggregation using COUNT SQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Data aggregations are complex mathematical operations that combine multiple numeric values \u200b\u200bto produce a single aggregate value.&nbsp;They are common in databases and are used to summarize and analyze complex information in a table.&nbsp;Thus, these operations can include sum, average, count, and other mathematical functions that help you analyze and better understand the data in a table.&nbsp;Data aggregations are an important part of data manipulation and are used to extract valuable insights and useful information from data stored in a database.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignleft is-resized\"><img decoding=\"async\" src=\"https:\/\/www.homehost.com.br\/blog\/wp-content\/uploads\/2023\/10\/image.png\" alt=\"COUNT is related to data aggregations\" class=\"wp-image-11459\" style=\"aspect-ratio:1;width:109px;height:auto\"\/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_is_COUNT_SQL_related_to_data_aggregations\"><\/span>How is COUNT SQL related to data aggregations?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">COUNT SQL is related to data aggregations because it is an aggregation function that combines numeric values \u200b\u200bto produce a single aggregate value.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">An SQL query&#8217;s table can contain multiple columns and rows, and data aggregations allow you to summarize and analyze that data in an easier-to-understand way.&nbsp;COUNT SQL is one of the most common aggregate functions in SQL and is often used to count the number of rows in a table or subquery.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example, suppose we have a sales table that contains information about the products sold, the date of sale, and the total value of the sale.&nbsp;This way, to get an overview of sales. We can use the COUNT function to count the total number of sales in the table:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT COUNT(*) FROM sales;<\/code><\/span><\/pre>\n\n\n<p class=\"wp-block-paragraph\">So, this will return a single value, which we can use to calculate other sales metrics, such as the number of sales per month or per product category.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Furthermore, COUNT can also be applied to count the number of rows in a subquery.&nbsp;Thus, this is useful when we want to get detailed information about a subset of data in a table.&nbsp;For example:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT COUNT(*) FROM (SELECT * FROM sales WHERE date &gt;= <span class=\"hljs-string\">'2022-01-01'<\/span> AND date &lt;= <span class=\"hljs-string\">'2022-02-28'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p class=\"wp-block-paragraph\">This will return the number of sales made in the month of January 2022.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Usage_examples_in_data_aggregations\"><\/span>Usage examples in data aggregations<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">There are many examples of how COUNT SQL can be applied to data aggregations, here are some examples:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Total record count:<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT COUNT(*) FROM table;<\/code><\/span><\/pre>\n\n\n<p class=\"wp-block-paragraph\">This command returns the total number of records in the table \u201ctable\u201d.<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li>Record count by category:<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT category , COUNT(*) <span class=\"hljs-keyword\">as<\/span> count FROM table GROUP BY category ;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p class=\"wp-block-paragraph\">Thus, the command returns the number of records in each category in the \u201ctable\u201d table.<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li>Record count per month:<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT MONTH(date) <span class=\"hljs-keyword\">as<\/span> mes, COUNT(*) <span class=\"hljs-keyword\">as<\/span> count FROM table GROUP BY MONTH(date);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p class=\"wp-block-paragraph\">This way, the command returns the number of records in each month in the \u201cdate\u201d column of the \u201ctable\u201d table.<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"4\">\n<li>Record count by city and category:<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT city , category, COUNT(*) <span class=\"hljs-keyword\">as<\/span> count FROM table GROUP BY city, category ;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p class=\"wp-block-paragraph\">This command returns the number of records in each city and category in the \u201ctable\u201d table.<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"5\">\n<li>Record count by city and month:<\/li>\n<\/ol>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT city, MONTH(date) <span class=\"hljs-keyword\">as<\/span> month, COUNT(*) <span class=\"hljs-keyword\">as<\/span> count FROM table GROUP BY city, MONTH(date);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p class=\"wp-block-paragraph\">This command returns the number of records in each city and month in the \u201cdate\u201d column of the \u201ctable\u201d table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Examples_using_COUNT_in_conjunction_with_other_functions\"><\/span>Examples using COUNT in conjunction with other functions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Here are some examples of how to use the COUNT function in conjunction with other functions in SQL:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1_Count_the_number_of_sales_by_state\"><\/span>1. Count the number of sales by state<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose we have a table called \u201csales\u201d that contains information about each sale made by the company, including the sale ID, customer status, sale amount, and number of items purchased.&nbsp;So, To count the number of sales for each state. We can use the COUNT function in conjunction with the <a href=\"https:\/\/www.copahost.com\/blog\/group-by-sql\/\">GROUP BY<\/a> function and the COUNT function again.&nbsp;Here is an example of how to do this:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT country, COUNT(*) <span class=\"hljs-keyword\">as<\/span> total_sales\nFROM sales\nGROUP BY country;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2_Count_the_number_of_sales_by_product_category\"><\/span>2. Count the number of sales by product category<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose we have a table called \u201csales\u201d that contains information about each sale made by the company, including the sale ID, product category, sale value, and number of items purchased.&nbsp;This way, to count the number of sales for each product category, we can use the COUNT function in conjunction with the <a href=\"https:\/\/www.copahost.com\/blog\/group-by-sql\/\">GROUP BY<\/a> function and the COUNT function again.&nbsp;Here is an example of how to do this:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT product_category, COUNT(*) <span class=\"hljs-keyword\">as<\/span> total_sales\nFROM sales\nGROUP BY product_category;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3_Count_the_number_of_sales_per_time_period\"><\/span>3. Count the number of sales per time period<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose we have a table called \u201csales\u201d that contains information about each sale made by the company, including the sale ID, date of sale, sales amount, and number of items purchased.&nbsp;Therefore, to count the number of sales for each time period, we can use the COUNT function in conjunction with the DATEDIFF function and the WHERE function.&nbsp;Here is an example of how to do this:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT DATEDIFF(day, <span class=\"hljs-string\">'2022-01-01'<\/span>, sale.date_venda) <span class=\"hljs-keyword\">as<\/span> period, COUNT(*) <span class=\"hljs-keyword\">as<\/span> total_sales\nFROM sales\nGROUP BY DATEDIFF(day, <span class=\"hljs-string\">'2022-01-01'<\/span>, sale.date_sale);\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"4_Finding_the_companys_oldest_customer\"><\/span>4. Finding the company&#8217;s oldest customer<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose we have a table called \u201ccustomers\u201d that contains information about each company&#8217;s customer, including the customer ID, customer name, and customer age.\u00a0In this sense, to find the company&#8217;s oldest customer. We can use the COUNT function in conjunction with the\u00a0<a href=\"https:\/\/www.copahost.com\/blog\/join-sql\/\">JOIN<\/a>\u00a0function and the WHERE function.\u00a0In addition, we can also apply the\u00a0<a href=\"https:\/\/www.copahost.com\/blog\/case-when-sql\/\">CASE WHEN<\/a>\u00a0and\u00a0<a href=\"https:\/\/www.copahost.com\/blog\/delete-into-sql\/\">DELETE<\/a>\u00a0function in conjunction with COUNT.Here is an example of how to do this:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT c.name <span class=\"hljs-keyword\">as<\/span> client, c.age <span class=\"hljs-keyword\">as<\/span> age\nFROM client c\nJOIN (SELECT id, MAX(age) <span class=\"hljs-keyword\">as<\/span> older_age\n      FROM client\n      GROUP BY id) m\nON c.id = m.id AND c.age= m.older_age<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5_Finding_the_customer_who_made_the_highest_value_sale\"><\/span>5. Finding the customer who made the highest value sale<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Suppose we have a table called \u201csales\u201d that contains information about each sale made by the company, including the sale ID, customer name, sale amount, and date of sale.&nbsp;Thus, to find the customer who made the sale with the highest value. We can use the COUNT function in conjunction with the&nbsp;<a href=\"https:\/\/www.copahost.com\/blog\/join-sql\/\">JOIN<\/a>&nbsp;function and the WHERE function.&nbsp;In that sense, here is an example of how to do this:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT c.name <span class=\"hljs-keyword\">as<\/span> client, v.value <span class=\"hljs-keyword\">as<\/span> value_sales\nFROM sales v\nJOIN client c ON v.client_id = c.id\nWHERE v.value = (SELECT MAX(value) FROM sales WHERE client_id = v.client_id)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The COUNT command allows you to count the number of records that satisfy a specific condition&nbsp;in a search.&nbsp;COUNT&nbsp;SQL&nbsp;is widely used in various contexts. From sales and stock analyzing to the evaluation of systems and processes. In this article, we will explore COUNT, addressing its forms of use. Also, characteristics and applications in different sectors and [&hellip;]<\/p>\n","protected":false},"author":17,"featured_media":3825,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[91],"tags":[],"class_list":["post-3813","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>COUNT SQL: how to count records in SQL - Copahost<\/title>\n<meta name=\"description\" content=\"Learn how to use COUNT in SQL to count records in a database, exploring its uses and applications in data analysis!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.copahost.com\/blog\/count-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"COUNT SQL: how to count records in SQL - Copahost\" \/>\n<meta property=\"og:description\" content=\"Learn how to use COUNT in SQL to count records in a database, exploring its uses and applications in data analysis!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.copahost.com\/blog\/count-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Copahost\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-01T12:55:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-19T12:49:33+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1024\" \/>\n\t<meta property=\"og:image:height\" content=\"866\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Schenia T\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Schenia T\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/\"},\"author\":{\"name\":\"Schenia T\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f\"},\"headline\":\"COUNT SQL: how to count records in SQL\",\"datePublished\":\"2023-11-01T12:55:36+00:00\",\"dateModified\":\"2023-11-19T12:49:33+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/\"},\"wordCount\":1453,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png\",\"articleSection\":[\"MySQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.copahost.com\/blog\/count-sql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/\",\"url\":\"https:\/\/www.copahost.com\/blog\/count-sql\/\",\"name\":\"COUNT SQL: how to count records in SQL - Copahost\",\"isPartOf\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png\",\"datePublished\":\"2023-11-01T12:55:36+00:00\",\"dateModified\":\"2023-11-19T12:49:33+00:00\",\"description\":\"Learn how to use COUNT in SQL to count records in a database, exploring its uses and applications in data analysis!\",\"breadcrumb\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.copahost.com\/blog\/count-sql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/#primaryimage\",\"url\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png\",\"contentUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png\",\"width\":1024,\"height\":866,\"caption\":\"count sql\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.copahost.com\/blog\/count-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.copahost.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"COUNT SQL: how to count records in SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#website\",\"url\":\"https:\/\/www.copahost.com\/blog\/\",\"name\":\"Copahost\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.copahost.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#organization\",\"name\":\"Copahost\",\"url\":\"https:\/\/www.copahost.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png\",\"contentUrl\":\"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png\",\"width\":223,\"height\":40,\"caption\":\"Copahost\"},\"image\":{\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f\",\"name\":\"Schenia T\",\"description\":\"Data scientist, passionate about technology tools and games. Undergraduate student in Statistics at UFPB. Her hobby is binge-watching series, enjoying good music working or cooking, going to the movies and learning new things!\",\"url\":\"https:\/\/www.copahost.com\/blog\/author\/schenia\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"COUNT SQL: how to count records in SQL - Copahost","description":"Learn how to use COUNT in SQL to count records in a database, exploring its uses and applications in data analysis!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.copahost.com\/blog\/count-sql\/","og_locale":"en_US","og_type":"article","og_title":"COUNT SQL: how to count records in SQL - Copahost","og_description":"Learn how to use COUNT in SQL to count records in a database, exploring its uses and applications in data analysis!","og_url":"https:\/\/www.copahost.com\/blog\/count-sql\/","og_site_name":"Copahost","article_published_time":"2023-11-01T12:55:36+00:00","article_modified_time":"2023-11-19T12:49:33+00:00","og_image":[{"width":1024,"height":866,"url":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png","type":"image\/png"}],"author":"Schenia T","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Schenia T","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.copahost.com\/blog\/count-sql\/#article","isPartOf":{"@id":"https:\/\/www.copahost.com\/blog\/count-sql\/"},"author":{"name":"Schenia T","@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f"},"headline":"COUNT SQL: how to count records in SQL","datePublished":"2023-11-01T12:55:36+00:00","dateModified":"2023-11-19T12:49:33+00:00","mainEntityOfPage":{"@id":"https:\/\/www.copahost.com\/blog\/count-sql\/"},"wordCount":1453,"commentCount":0,"publisher":{"@id":"https:\/\/www.copahost.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.copahost.com\/blog\/count-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png","articleSection":["MySQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.copahost.com\/blog\/count-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.copahost.com\/blog\/count-sql\/","url":"https:\/\/www.copahost.com\/blog\/count-sql\/","name":"COUNT SQL: how to count records in SQL - Copahost","isPartOf":{"@id":"https:\/\/www.copahost.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.copahost.com\/blog\/count-sql\/#primaryimage"},"image":{"@id":"https:\/\/www.copahost.com\/blog\/count-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png","datePublished":"2023-11-01T12:55:36+00:00","dateModified":"2023-11-19T12:49:33+00:00","description":"Learn how to use COUNT in SQL to count records in a database, exploring its uses and applications in data analysis!","breadcrumb":{"@id":"https:\/\/www.copahost.com\/blog\/count-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.copahost.com\/blog\/count-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.copahost.com\/blog\/count-sql\/#primaryimage","url":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png","contentUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2023\/10\/image-21.png","width":1024,"height":866,"caption":"count sql"},{"@type":"BreadcrumbList","@id":"https:\/\/www.copahost.com\/blog\/count-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.copahost.com\/blog\/"},{"@type":"ListItem","position":2,"name":"COUNT SQL: how to count records in SQL"}]},{"@type":"WebSite","@id":"https:\/\/www.copahost.com\/blog\/#website","url":"https:\/\/www.copahost.com\/blog\/","name":"Copahost","description":"","publisher":{"@id":"https:\/\/www.copahost.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.copahost.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.copahost.com\/blog\/#organization","name":"Copahost","url":"https:\/\/www.copahost.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png","contentUrl":"https:\/\/www.copahost.com\/blog\/wp-content\/uploads\/2016\/03\/copahostlogo.png","width":223,"height":40,"caption":"Copahost"},"image":{"@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.copahost.com\/blog\/#\/schema\/person\/2efb96f9dfaf6162f347abcd06b1429f","name":"Schenia T","description":"Data scientist, passionate about technology tools and games. Undergraduate student in Statistics at UFPB. Her hobby is binge-watching series, enjoying good music working or cooking, going to the movies and learning new things!","url":"https:\/\/www.copahost.com\/blog\/author\/schenia\/"}]}},"_links":{"self":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts\/3813","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/users\/17"}],"replies":[{"embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/comments?post=3813"}],"version-history":[{"count":17,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts\/3813\/revisions"}],"predecessor-version":[{"id":3905,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/posts\/3813\/revisions\/3905"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/media\/3825"}],"wp:attachment":[{"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/media?parent=3813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/categories?post=3813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.copahost.com\/blog\/wp-json\/wp\/v2\/tags?post=3813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}