Aggregate Queries in SQL

Aggregate Queries in Oracle

Aggregate Queries in Oracle work on multiple rows unlike normal functions which work on single rows.

There are 5 aggregate functions in SQL. They are:-

1)      Max
Max works on all data types for which ordering is defined.For numerical types value based ordering is done, for character types dictionary ordering is use, for Date & Time the instance that later is considered to be greater.

2)      Min --- Same as above

3)      Sum Totals the data. Only works for numerical types.

4)      Avg finds the average. Only works for numerical types.

5)      Count .Counts the number of records. Works for all data types.

We create the following table to illustrate the concepts.

create table Cricket_Scores (batsman varchar(100),InningsNo int,MatchType varchar(10),score int)

Finally we have the table with the following records.
We try the Queries one by one:-

Using a aggregate query with other fields.
Let us try the following query:-

In such cases we need to specify the group by option

No Oracle creates groups based on batsman and calculates max based on those groups separately.

This gives the batsman wise statistics.
Multiple group by clauses can be used.

The order of group by clauses will not affect the output.

It does affect the processing time. So, we should specify the groups such that sizes are reduced in the beginning.

Using the having clause.

The having clause is used for providing conditions based on aggregate queries.

Suppose we wish to find the player with max score greater than equal to 100.

A query containing where, group by,and having clauses.

Some questions:-
Find the highest scores in Test Matches and One Days

Find the highest score in the 1^st innings of a batsman:-
Removing the group by on batsman we get.

Find the batsman with highest average in tests.

Assignments

Create the following table

Marksheet
(rollno primary key, name, physics, chemistry, maths)
Add the following data
1, A,60,59,75
2, B,43,24,70
3, C,55,67,49
4, A,61,49,47
Find the following data

Highest scores in all subjects
Student with highest marks in physics
Student with highest total marks
Student with highest difference between physics and chemistry marks
List of students with common names

Make the following table

receiptno   qty      sale_date      share        price


    1        25          2022-04-15  Tata         500
    2        30          2022-04-25  Tata         450    3         5          2022-04-15  Bata         300
    4         6          2022-04-25  Bata         200
    5        25          2022-04-15  BMW       400