Include Exclude Where Clause Based on Parameter in Sql

---

SQL: Counting Social function

This SQL teacher explains how to use the SQL COUNT function with syntax, examples, and practice exercises.

Verbal description

The SQL Count down function is ill-used to count the routine of rows returned in a SELECT program line.

Syntax

The phrase structure for the COUNT function in SQL is:

Take COUNT(aggregate_expression) FROM tables [WHERE conditions] [Grade Aside expression [ ASC | DESC ]];

OR the syntax for the COUNT function when grouping the results by one or more columns is:

Prime expression1, expression2, ... expression_n,        Reckon(aggregate_expression) FROM tables [WHERE conditions] Radical BY expression1, expression2, ... expression_n [ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are non encapsulated within the COUNT subprogram and moldiness be included in the GROUP BY clause at the oddment of the SQL statement.

aggregate_expression

This is the column or expression whose not-null values will be counted.

tables

The tables that you wish to recollect records from. There must be at to the lowest degree one tabular array listed in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be elite.

Enjoin BY expression

Optional. The expression used to sort the records in the upshot fit. If more one expression is provided, the values should be comma spaced.

ASC

Optional. ASC sorts the result set in ascending order by expression. This is the default demeanour, if no modifier is provider.

DESC

Optional. DESC sorts the result set in descendant order past expression.

DDL/DML for Examples

If you want to follow along with this teacher, get the DDL to create the tables and the DML to populate the information. Then try the examples in your possess database!

Get DDL/DML

Example - COUNT Function only includes NOT NULL Values

Non everyone realizes this, but the Count down function will only count the records where the expression is NOT NULL in COUNT(face). When the expression is a NULL value, IT is non included in the COUNT calculations. Let's explore this further.

In this example, we have a table called customers with the following information:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Kate Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Samuel Johnson	Derek	techonthenet.com

Enter the following Superior statement that uses the COUNT function:

Try It

SELECT Matter to(customer_id) FROM customers;

There will be 1 record selected. These are the results that you should see:

In this example, the query testament return 6 since there are 6 records in the customers table and all customer_id values are NOT NULL (IE: customer_id is the primary key for the table).

But what happens when we find a Zip value with the COUNT function? Let's go in this next SELECT affirmation that counts the favorite_website column which can contain NULL values:

Try It

SELECT Bet(favorite_website) FROM customers;

There will be 1 book chosen. These are the results that you should see:

COUNT(favorite_website)
5

This back example will return 5. Because one of the favorite_website values is NULL, it would be excluded from the COUNT occasion calculation. As a solution, the query will return 5 as an alternative of 6.

Tap: Use the primary key in the COUNT function operating theater COUNT(*) if you want to follow sure that records aren't excluded in the calculations.

Example - Victimization a Single Expression in the COUNT Procedure

Let's deal an illustration that shows how to use the COUNT procedure with a single expression in a inquiry.

In this example, we suffer a set back called employees with the following data:

employee_number	last_name	first_name	salary	dept_id
1001	Julia Evelina Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

Enter the favourable SQL statement:

Try It

Prime COUNT(*) AS total FROM employees WHERE salary > 50000;

There will be 1 record selected. These are the results that you should see:

In that example, we will return the number of employees who have a wage above $50,000. We've aliased the COUNT(*) as total to make our query results more readable. Now, tote up will display as the column heading when the resolution Seth is returned.

Example - Using GROUP BY with the COUNT Function

In some cases, you will be required to use the GROUP BY clause with the COUNT use. This happens when you have columns catalogued in the SELECT command that are not disunite of the Matter function. Let's explore this further.

Again, using the employees table populated with the following information:

employee_number	last_name	first_name	salary	dept_id
1001	Bessie Smith	Whoremaster	62000	500
1002	Anderson	Jane	57500	500
1003	Mt. Everest	Brad	71000	501
1004	Horvath	Jackass	42000	501

Enter the favorable SQL command:

Try IT

SELECT dept_id, COUNT(*) AS total FROM employees WHERE salary > 50000 GROUP BY dept_id;

Thither will beryllium 2 records designated. These are the results that you should see:

dept_id	total
500	2
501	1

Therein example, the COUNT function will return the number of employees that make over $50,000 for each dept_id. Because the dept_id chromatography column is non included in the COUNT function, it must be listed in the GROUP BY clause.

Example - Victimization DISTINCT with the Reckon Function

Did you know that you privy use the DISTINCT clause inside the Matter purpose? This allows you to count entirely the unique values.

Using the same employees table as the early exemplar:

employee_number	last_name	first_name	earnings	dept_id
1001	Smith	Lavatory	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Labourer	42000	501

Introduce the following SQL affirmation:

Try on Information technology

Choice COUNT(DISTINCT dept_id) AS total FROM employees WHERE salary > 50000;

There will constitute 1 record designated. These are the results that you should see:

Therein example, the COUNT occasion testament restoration the unique number of dept_id values that have at least same employee that makes over $50,000.

TIP: Performance Tuning with the Bet Function

Since the COUNT function will get back the same results regardless of what NOT NULL field(s) you include American Samoa the COUNT function parameters (ie: inside the parentheses), you posterior wont COUNT(1) to get better performance. Now the database engine volition not have to fetch any information William Claude Dukenfield, instead it will just retrieve the integer value of 1.

For example, instead of incoming this affirmation:

Try IT

SELECT dept_id, COUNT(*) AS add up FROM employees WHERE wage > 50000 GROUP BY dept_id;

You could replace Reckon(*) with COUNT(1) to beget major execution:

Try It

SELECT dept_id, COUNT(1) AS total FROM employees WHERE salary > 50000 GROUP BY dept_id;

Forthwith, the COUNT function does not deman to recover each fields from the employees table as information technology had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for apiece immortalis that meets your criteria.

Practice Exercises

If you want to test your skills victimisation the SQL COUNT function, try on more or less of our rehearse exercises.

These exercises allow you to try on out your skills with the Numeration purpose. You will follow given questions that you need to wor. After from each one exercise, we provide the solution so you can check your answer. Give IT a try!

Go to Practice Exercises

Include Exclude Where Clause Based on Parameter in Sql

Source: https://www.techonthenet.com/sql/count.php

Share this post