My SQL -----database---- What restrictions apply to the use of the aggregate functions within the SELECT...
My SQL -----database----
Following are the restrictions that apply to the aggregate functions within the select statement :
- Some of the aggregate functions(Sum and avg) can only be applied to numeric data.
- An aggregate function with a non-aggregate can't be used simultaneously in a single sql query.
- It usually ignores null values.
Null values are all ignored by the aggregate functions except in the case of count function.
Example: consider a table instructors as follows
Name Department salary Age Arun Prakash cs 40000 45 sam allans ee 30000 52 charlie white cs 20000 57 joseph ee null 35 piyush cs null 30 ram mohan it 20000 40
joseph and piyush are newly joined so there salary are null.
if we want the salary of all the instructors paid by the college we can write the query :
select sum(salary) from instructor;
Group by clause is used when we have to apply aggregate functions for a group of set of tuples, it first groups the table according to some attribute and then apply the aggregate function on each group.
The where clause is used when we have to apply condition to tuples while the having clause is used when we have to apply condition to a group.
Example : if we want to find the average salary of all the departments where average salary is greater then 25000 we write the following query:
select department, avg(salary) from instructor group by department having avg(salary) > 25000;
department avg(salary) cs 30000 ee 30000