How to query the number of data in oracle

Oracle can query the number of data using the COUNT function, the GROUP BY clause, the HAVING clause, and the subquery. 1. Use the COUNT function, its basic syntax is as follows: SELECT COUNT(column_name) FROM table_name; 2. Use the GROUP BY clause to group the data according to the value of a certain column, and count the number of data in each group; 3. Use the HAVING sub and so on.

The operating environment of this tutorial: Windows 10 system, Oracle version 19c, DELL G3 computer.

Oracle is a relational database management system used to store and manage large amounts of structured data. In daily database operations, we often encounter situations where we need to query the amount of data. This article will introduce how to use Oracle to query data quantity.

1. Use the COUNT function to query the number of data

The COUNT function is a function used in Oracle database to calculate the number of data in a table. The basic syntax is as follows:

SELECT COUNT(column_name) FROM table_name;

Among them, column_name represents the name of the column that needs to be counted, and table_name represents the name of the table that needs to be queried.

Example:

We have a table named employees, which contains columns such as employee_id, first_name, last_name and salary. Now, if we want to count the number of employees in the table, we can execute the following query:

SELECT COUNT(employee_id) FROM employees;

This query statement will return the number of employees in the employees table.

2. Use the GROUP BY clause to query the number of data in multiple columns

Sometimes we also need to group the data according to the value of a certain column and count each The amount of data in the group. In this case, you can use the GROUP BY clause with the COUNT function.

The GROUP BY clause is used to group data according to specified columns. For example, if we want to count the number of employees in each department in the employees table, we can execute the following query:

SELECT department_id, COUNT(employee_id) FROM employees GROUP BY department_id;

This query statement will return the department_id of each department and the corresponding number of employees.

3. Use the HAVING clause to filter the number of data

Sometimes we need to further filter the query results and only return the number of data that meets the conditions. In this case, you can use the HAVING clause.

HAVING clause is used in GROUP Filter based on BY clause. For example, if we want to count the number of employees in each department in the employees table whose salary is greater than 10,000, we can execute the following query:

SELECT department_id, COUNT(employee_id) FROM employees WHERE salary > 10000 GROUP BY department_id;

This query statement will return the number of employees in each department whose salary is greater than 10,000.

4. Use subquery to query the quantity of data

Sometimes we need to query the data that meets the conditions based on certain conditions, and then count the quantity. In this case, you can use a subquery.

A subquery is a query statement nested inside the main query. For example, if we want to count the number of employees in the employees table whose salary is greater than the average salary, we can execute the following query:

SELECT COUNT(employee_id) FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

This query statement will return the number of employees whose salary is greater than the average salary.

Summary:

This article introduces the method of querying the number of data in Oracle database, including using the COUNT function and GROUP BY clause, HAVING clause and subquery. These methods can meet the needs of different query scenarios, and through flexible use, the amount of data can be queried more efficiently. In practical applications, choosing the appropriate method for querying based on specific circumstances will help improve query efficiency and the accuracy of data statistics. .

The above is the detailed content of How to query the number of data in oracle. For more information, please follow other related articles on the PHP Chinese website!