SQL COUNT

3 Key excerpts on "SQL COUNT"

• 

  eBook - ePub

  Data Wrangling with SQL

  A hands-on guide to manipulating, wrangling, and engineering data using SQL

  • Raghav Kandarpa, Shivangi Saxena(Authors)
  • 2023(Publication Date)
  • Packt Publishing

    (Publisher)

  following query: SELECT COUNT(*) FROM orders;

  In this example, the COUNT() function is used to count the total number of rows in the orders table. * is used as a wildcard to select all the columns. This is used when we don’t have a specific column to count.

  The result of this query would be a single value representing the total number of rows in the orders table.

  Case scenario

  The COUNT() function in SQL can be used by a retail company that wants to track the number of products sold per day. The company has a database table called sales that contains information about each product sale, including the date of the sale and the product ID.

  sale_id	product_id	sale_date
  1	101	1/24/2023
  2	102	1/24/2023
  3	103	1/24/2023
  4	101	1/25/2023
  5	104	1/25/2023
  6	105	1/25/2023
  7	101	1/26/2023
  8	103	1/26/2023
  9	105	1/26/2023
  10	101	1/27/2023
  11	102	1/27/2023
  12	103	1/27/2023
  13	104	1/27/2023
  14	105	1/27/2023

  Figure 8.9 – sales table

  To find the number of products sold per day, the company would use the COUNT() function in a SQL query that groups the data by date and counts the number of rows in each group. The query would look something like this:

  SELECT  sale_date,  COUNT(*) as count_products FROM sales GROUP BY date;

  This query would return a result set that shows the number of products sold each day, allowing the company to track sales trends over time and make informed business decisions.

  sale_date	count_products
  1/24/2023	3
  1/25/2023	3
  1/26/2023	3
  1/27/2023	5

  Figure 8.10 – Query result

  An interesting example of using the COUNT() function in SQL could be in the social media industry, where a company wants to analyze the engagement of its users. The company may have a table that contains user information, including the user_id, registration_date, and the number_of_posts.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2010(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  There are two options, ALL and DISTINCT, that are shown as keywords inside the parameter list. The keyword ALL is optional and is never really used in practice. It says that all the rows in the working column are retained for the final calculation. The keyword DISTINCT is not optional in these functions. It removes all redundant duplicates values from a working column before the final calculation. Let’s look at the particulars of each aggregate function.

  21.1 COUNT() Functions

  There are two forms of the COUNT () function: cardinality and expression counting.

  COUNT (*) returns the number of rows in a table (called the cardinality of the table, in relational terms); it is the only standard aggregate function that uses an asterisk as a parameter. This function is very useful and usually runs quite fast, since it can use system information about the table size. Remember that NULL values are also counted, because this function deals with entire rows and not column values. There is no such thing as “NULL row”—a row exists or it does not, without regard to contents.

  An empty table has a COUNT (*) of zero, which makes sense. However, all of the other aggregate functions we will discuss in this section will return an empty set as the result of being given an empty set to operate on–"ab nilo, ex nilo. " The cardinality is a property of the set as a whole. Summation, average, and so forth are computations done on the elements of the set; if that set is empty, they have no elements from which to build an answer. While it is too late to change SQL, we would have been better off with syntax that uses a table expression in a parameter for cardinality, much like the EXISTS () predicate.

  You would think that using the COUNT (*) would be easy, but there are a lot of subtle tricks to it. Think of a database of the presidencies of the United States, with columns for the first name, middle initial(s), and last name of each U. S. President, along with his political party and his terms in office. It might look like this:

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Microsoft SQL Server 2012 Programming

  • Paul Atkinson, Robert Vieira(Authors)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  COUNT(*) function is about counting the rows in a query. To begin with, let’s go with one of the most common varieties of queries:

  SELECT COUNT(*) FROM HumanResources.Employee WHERE HumanResources.Employee.BusinessEntityID = 5;

  Code snippet Chap03.sql

  The record set you get back looks a little different from what you’re used to in earlier queries: ----------- 1 (1 row(s) affected)

  Let’s look at the differences. First, as with all columns that are returned as a result of a function call, there is no default column name. If you want there to be a column name, you need to supply an alias. Next, you’ll notice that you haven’t really returned much of anything. So what does this record set represent? It is the number of rows that matched the WHERE condition in the query for the table(s) in the FROM clause.

  NOTE Keep this query in mind. This is a basic query that you can use to verify that the exact number of rows that you expect to be in a table and match your WHERE condition are indeed in there.

  Just for fun, try running the query without the WHERE clause:

  SELECT COUNT(*) FROM HumanResources.Employee;

  Code snippet Chap03.sql

  If you haven’t done any deletions or insertions into the Employee table, you should get a record set that looks something like this:

  ----------- 290 (1 row(s) affected)

  What is that number? It’s the total number of rows in the Employee table. This is another one to keep in mind for future use.

  Now, you’re just getting started! If you look back at the header for this section (the COUNT section), you’ll see that there are two ways of using COUNT . I’ve already discussed using COUNT

  Sign up to read

  Learn more about book