To study and write aggregation functions, group by and having clause with suitable examples.

*An aggregate function is a function that performs a calculation on a set of values, and returns a single value. *Aggregate functions are often used with the GROUP BY clause of the SELECT statement.

1. MIN() - returns the smallest value within the selected column Syntax: SELECT MIN(column_name) FROM table_name WHERE condition;
2. MAX() - returns the largest value within the selected column Syntax: SELECT MAX(column_name) FROM table_name WHERE condition; 3.COUNT() - returns the number of rows in a set Syntax: SELECT COUNT(column_name) FROM table_name WHERE condition;
3. SUM() - returns the total sum of a numerical column Syntax: SELECT SUM(column_name) FROM table_name WHERE condition; 5.AVG() - returns the average value of a numerical column Syntax: SELECT AVG(column_name) FROM table_name WHERE condition;

GROUP BY: This query is used to group all the records in a relation together for each and every value of a specific key(s) and then display them for a selected set of fields in the relation. Syntax: SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. Syntax: SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

#PROCEDURE:

1. Start the program.
2. Read the given query.
3. Perform the given aggregation function using MIN(),MAX(),COUNT(),SUM(),AVG().
4. To group all the records in a relation together by GROUP BY clause. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s);
5. To specify the conditions in a grouped records by HAVING clause. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition;
6. Show the output
7. Stop the program

SELECT SUM(workhour) AS "Total working hours" FROM employee1;

SELECT COUNT(DISTINCT salesman_id) AS COUNT FROM orders;

SELECT name, MAX(LENGTH(name)) AS length FROM customer;

SELECT 
    CASE
        WHEN (julianday('now') - julianday(DateOfBirth)) / 365.25 < 20 THEN 'Under 20'
        WHEN (julianday('now') - julianday(DateOfBirth)) / 365.25 BETWEEN 20 AND 30 THEN '20-30'
        WHEN (julianday('now') - julianday(DateOfBirth)) / 365.25 BETWEEN 31 AND 40 THEN '31-40'
        WHEN (julianday('now') - julianday(DateOfBirth)) / 365.25 BETWEEN 41 AND 50 THEN '41-50'
        ELSE 'Above 50'
    END AS AgeGroup,
    COUNT(*) AS TotalPatients
FROM Patients
GROUP BY AgeGroup;

SELECT DoctorID, COUNT(*) AS TotalAppointments
FROM Appointments
GROUP BY DoctorID;

SELECT Specialty, Gender, COUNT(*) AS TotalDoctors FROM Doctors GROUP BY Specialty, Gender;

SELECT occupation, SUM(workhour)
FROM employee1
GROUP BY occupation
HAVING SUM(workhour) > 20;;

SELECT age, AVG(income)
FROM employee
GROUP BY age
HAVING AVG(income) BETWEEN 300000 AND 500000;

SELECT PatientID, COUNT(*) AS TotalRecords
FROM MedicalRecords
GROUP BY PatientID
HAVING COUNT(*) > 3;

SELECT category_id, AVG(Price)
FROM products
GROUP BY category_id
HAVING AVG(Price) BETWEEN 10 AND 15;

Thus the basic aggregation commands are executed.