Let’s combine the creation of a table with some aggregation functions.
We’ll
create a table named employees and then use some aggregation functions to
perform operations on the data.
Creating the employees Table
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
position VARCHAR(100),
salary DECIMAL(10, 2),
hire_date DATE
);
Inserting Data into the Table
INSERT INTO employees (name, position, salary, hire_date)
VALUES
('Alice', 'Manager', 80000, '2023-01-15'),
('Bob', 'Software Engineer', 60000, '2023-03-22'),
('Charlie', 'Software Engineer', 65000, '2023-05-10'),
('Diana', 'HR', 50000, '2023-07-05'),
('Eve', 'Manager', 90000, '2023-09-12');
Aggregation Function Examples
1. Total Salary of All Employees:
SELECT SUM(salary) AS total_salary
FROM employees;
This query calculates the total sum of all salaries in the employees table.
1. Average Salary of All Employees:
SELECT AVG(salary) AS average_salary
FROM employees;
This query calculates the average salary of all employees.
1. Count of Employees by Position:
SELECT position, COUNT(*) AS num_employees
FROM employees
GROUP BY position;
This query counts the number of employees for each position.
1. Highest and Lowest Salary:
SELECT MAX(salary) AS highest_salary, MIN(salary) AS
lowest_salary
FROM employees;
This query finds the highest and lowest salaries among all employees.
Example Output (Aggregated Data)
After running the above aggregation queries, you might get results similar
to these:
1. Total Salary: | Total Salary | |————–| | 345000 |
2. Average Salary: | Average Salary | |—————-| | 69000.00 |
3. Count of Employees by Position: | Position | Num Employees | |
——————-|—————| | Manager | 2 | | Software Engineer | 2 | | HR |
1|
4. Highest and Lowest Salary: | Highest Salary | Lowest Salary | |
—————-|—————| | 90000 | 50000 |
These examples demonstrate how to create a table, insert data, and perform
aggregation functions to analyze the data. Let me know if there’s anything
specific you’d like to explore further!