Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Group Functions

SQL Group Functions
Sum
Avg
Max
Min
Count

Group functions will be applied on all the rows but produces single output.

a) SUM

     This will give the sum of the values of the specified column.

     Syntax: sum (column)

     Ex:
          SQL> select sum(sal) from emp;

  SUM(SAL)
   ----------
     38600

b) AVG

     This will give the average of the values of the specified column.

     Syntax: avg (column)

     Ex:
          SQL> select avg(sal) from emp;

   AVG(SAL)
   ---------------
   2757.14286

c) MAX

     This will give the maximum of the values of the specified column.

     Syntax: max (column)

     Ex:
          SQL> select max(sal) from emp;

    MAX(SAL)
   ----------
     5000
d) MIN

     This will give the minimum of the values of the specified column.

     Syntax: min (column)

     Ex:
          SQL> select min(sal) from emp;

   MIN(SAL)
   ----------
     500
e) COUNT

     This will give the count of the values of the specified column.

     Syntax: count (column)

     Ex:
          SQL> select count(sal),count(*) from emp;

COUNT(SAL)   COUNT(*)
--------------    ------------

        14                     14
Share this article :

2 comments:

  1. Hi There,


    This is indeed great! But I think perhaps you are generally referring SQL Group Functions which is getting unsustainable.

    Can anyone suggest on TEMP table concept. I have below requirement and I tried the below but I am getting error.

    select partyno into #tmptbl
    from partym --------- when I executed this I got ORA-00911: invalid character error.

    my requirement is :
    I need to execute 3 queries one by one and hold the result of the first query and second query to feed as input to 3rd query. at the end of the job I need to delete first query and second query result.

    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Gracias
    Preethi

    ReplyDelete
  2. Hi There,

    Such vivid info on the SQL Group Functions! Flabbergasted! Thank you for making the read a smooth sail!

    for non rac servers, the query works and

    for rac servers
    SELECT host_name,count(*)
    FROM (SELECT b.host_name, c.VALUE
    FROM gv$session a,
    gv$instance b,
    (SELECT TO_NUMBER (
    ROUND (
    TO_CHAR (
    (CURRENT_UTILIZATION / LIMIT_VALUE) * 100),
    0))
    VALUE
    FROM gv$resource_limit
    WHERE resource_name LIKE '%proces%') c
    WHERE a.inst_id = b.inst_id AND c.VALUE >= 80)
    GROUP BY host_name
    /
    SELECT host_name,count(*)
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server PZ99, instance
    zimgrid45.vzbi.com:dev1d (2)
    ORA-01722: invalid number.

    Follow my new blog if you interested in just tag along me in any social media platforms!

    Many Thanks,
    Preethi.

    ReplyDelete