DBMS > Structural Query Language (SQL) > Aggregate Functions

 

In DBMS, an aggregate function is a function appliedon a single column where the values of multiple rows are as input  to form a single value of more significant meaning. column values may grouped further on certain criteria to create group-wise results.

 

Some  Example of Aggregate Functions are:

1) Count()
2) Sum()
3) Avg()
4) std()
5) Min()
6) Max()

Now let us understand each Aggregate function with a example. 
The following is the item table with data:


1. To find number rows and average o f all products:

 select count(price), avg(price) from item 

1. To find items which price are highest

     select name,price from item

       WHERE price=
                            (select max(price) from item)

2. To find items which price are lowest

         select name,price from item

              WHERE  price=
                                    (select min(price) from item)

3. To find items which price are lower than average price

    select name,price from item

         WHERE  price<
                               (select avg(price) from item)

4. List distinct type of item 
       select distinct(name) from item

5. Show item-wise average
select name, avg(price) from item
group by name

6. Show item-wise average and number of item
select name, count(name), avg(price)
from item
group by name

7. Show item-wise average and number of item
with appropriate heading
select name,
count(name) as Number ,
avg(price) as Mean
from item
group by name

8. How many item's price are less than 50?
select name, price from item
where price < 50

select count(*) from item
where price < 50

 

 

Prev

Write SQL to create the database and tables.

Next

Use of Procedure in Mysql

Feedback

Submit