Sybase aggregation query
Sybase databases support multiple aggregated queries, including but not limited to the following:
1. COUNT: Counts the number of rows under specified conditions.
Example: Calculate the number of boys in a student table.
Table structure:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
gender VARCHAR(10)
);
Sample data:
INSERT INTO students (id, name, gender)
VALUES
(1, 'Alice', 'Female'),
(2, 'Bob', 'Male'),
(3, 'Charlie', 'Male'),
(4, 'Eva', 'Female');
Query statement:
SELECT COUNT(*) AS male_count
FROM students
WHERE gender = 'Male';
Result: 2
2. SUM: Calculate the sum of a certain field under specified conditions.
Example: Calculate the total amount of all orders in an order table.
Table structure:
CREATE TABLE orders (
id INT PRIMARY KEY,
amount DECIMAL(10,2),
date DATE
);
Sample data:
INSERT INTO orders (id, amount, date)
VALUES
(1, 100.50, '2022-01-01'),
(2, 50.25, '2022-01-01'),
(3, 75.75, '2022-01-02');
Query statement:
SELECT SUM(amount) AS total_amount
FROM orders;
Result: 226.50
3. AVG: Calculate the average value of a field under specified conditions.
Example: Calculate the average score of all students in a student grade sheet.
Table structure:
CREATE TABLE scores (
id INT PRIMARY KEY,
student_id INT,
score INT
);
Sample data:
INSERT INTO scores (id, student_id, score)
VALUES
(1, 1, 90),
(2, 2, 80),
(3, 1, 95),
(4, 3, 85);
Query statement:
SELECT AVG(score) AS average_score
FROM scores;
Result: 87.5
4. MAX: Find the maximum value of a field under specified conditions.
Example: Find the order with the highest amount in an order table.
Query statement:
SELECT MAX(amount) AS max_amount
FROM orders;
Result: 100.50
5. MIN: Find the minimum value of a field under specified conditions.
Example: Find the student with the lowest score on a student's grade sheet.
Query statement:
SELECT MIN(score) AS min_score
FROM scores;
Result: 80
These aggregated queries can be combined and nested according to actual needs. Please note that the above examples are only for demonstration purposes, and the actual table structure and data may vary.
