STATISTICS

MySQL

Example

CREATE TABLE SampleTable (
    ID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

INSERT INTO SampleTable (ID, Name, Age) VALUES
(1, 'John', 25),
(2, 'Jane', 30),
(3, 'Doe', 35),
(4, 'Smith', 40),
(5, 'Johnny', 45);

SELECT AVG(Age) AS AverageAge FROM SampleTable;

Output

+------------+
| AverageAge |
+------------+
|     35.0   |
+------------+

Explanation

In the above example, a table named SampleTable is first created with three columns: ID, Name, and Age. Five records are inserted into this table.

The SQL function AVG() is used to calculate the average value of the Age column in the SampleTable. The output of the SELECT AVG(Age) query is 35.0, which is the average age of all the people in the SampleTable.

PostgreSQL

Example

CREATE TABLE example_table
(
    example_column1 INT,
    example_column2 VARCHAR(50)
);

INSERT INTO example_table
VALUES(1,'Test1'),(2,'Test2'),(3,'Test3'),(4,'Test4'),(5,'Test5'),(6,'Test6'),(7,'Test7');

ANALYZE example_table;

Output

CREATE TABLE
INSERT 0 7
ANALYZE

Explanation

In the example provided, a table named example_table is created with two columns, example_column1 and example_column2 of type INT and VARCHAR respectively. Several rows of data are then inserted into the table. Finally, the PostgreSQL ANALYZE command is used to collect statistics about the contents of tables in the database, which is important to help the PostgreSQL query planner to help make intelligent decisions during query optimization and execution. In this case, it’s collecting statistics for the table example_table. The output confirms the creation of the table, the insertion of the data, and the successful execution of the ANALYZE command.

SQL Server

Example

CREATE STATISTICS order_stats ON Orders(OrderID, CustomerID)

Output

Statistic 'order_stats' created on Orders.

Explanation

The above SQL Server command creates a statistics object called order_stats on the Orders table for the columns OrderID and CustomerID. These statistics provide SQL Server with metadata about the distribution of values in these columns and help enhance the performance of queries.

Oracle

Example

SELECT
    COUNT(*) as Total,
    SUM(salary) as SalarySum,
    AVG(salary) as SalaryAverage,
    MIN(salary) as SalaryMin,
    MAX(salary) as SalaryMax
FROM
    employees;

Output

Total | SalarySum | SalaryAverage | SalaryMin | SalaryMax
----------------------------------------------------------
100   | 2400000   | 24000          | 13500     | 33500

Explanation

The SQL query above retrieves statistical information about the data in the “salary” column of the “employees” table. It counts all rows (total), calculates sum, average, minimum, and maximum values of salaries. These functions provide a quick overview of the distribution of salaries in the employees table.