SQL Group by

SQL Group by

SQL Group by : In certain situations we would like to use aggregate functions, which are applicable to a group of set of tuples, rather than a single set of tuples, we specify this using GROUP BY clause. The attribute are attributes given in the GROUP BY clause are used to form groups. The tuples with same value on all attributes in the GROUP BY clause are placed in one group.
“Find the average account balance at each branch”
Select br_name, avg(balance) from account group by br_name
Sometimes, it is required to specify conditions that would operate on a group of tuples rather than on single tuple.  For example, if we are interested in only those branches where the average account balance is greater than Rs. 50000. Predicates of the having clause are applied after the formation of groups.
Select br_name, avg(balance) from account group by br_name having avg (balance)>50000.
Note: When we wish to treat the entire relation as one group, we should not use group by clause. SQL does not allow the use of DISTINCT with COUNT (*) AND ALSO max (AVG ()).

Using SQL Group by

IF a WHERE clause and HAVING clause appears in the same query, the predicates in the WHERE clause is applied first. Selected tuples satisfying the WHERE conditions are then put into group by the GROUP BY clause. The HAVING clause, if it is present, is then applied to each group, the groups that do not satisfy the HAVING clause conditions are omitted. The remaining groups are used by the SELECT clause to generate tuples of the result of the query.
To illustrate the use of both a HAVING clause and a WHERE clause in the same query,  we consider the query.
“Find the average balance for each customer who lives in MYSORE and has at least three accounts.”
Select depositor.cust_name, avg (balance)
From depositor, account, customer
Where depositor.account_no = account.account_no and
Depositor.cust_name = customer.cust_name and
Cust_city = “MYSORE”
Group by depositor.cust_name
Having account count (distinct.account_no).=3.

Leave a Reply

Your email address will not be published.