Groupby and Having

GROUP BY:- The GROUP BY clause is another section of the select statement. This optional clause tells oracle to group rows based on distinct values that exist for specified columns i.e. it creates a data set containing several set of records grouped together based on a condition.
Syn:-    select col_name,aggregatfunction(col-name),-- from table name
             GROUP BY Columnname;
Ex:-     create table products(code number(5),na varchar2(20),gid number(5),
            Qty number(5));
            Insert into products values(100,’surf’,10,20);
            Insert into products values(200,’LUX’,20,20);
            Insert into products values(100,’CINTHOL’,10,20);
            Insert into products values(100,’PONDS’,30,20);
            Insert into products values(100,’PEARS’,20,20);
            Select gid,sum(qty) from products group by (gid);

HAVING:-  The having clause can be used in conjunction with the GROUP BY clause. HAVING imposes a condition on the GROUP BY clause. This further filters the group created by the GROUP BY clause.
Syn:-    select col_name,aggregatfunction(col-name),-- from table name
             GROUP BY Columnname HAVING condition;
Ex:-  Select gid,sum(qty) from products group by (gid)
         having gid=10 or gid=30;

For
Online Classes

Contact Us: +919885348743