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

Pages

Thursday, 31 March 2016

SQL Group By And Having

GROUP BY

Using group by, we can create groups of related information.
Columns used in select must be used with group by, otherwise it was not a group by expression.

Ex:
     SQL> select deptno, sum(sal) from emp group by deptno;

    DEPTNO   SUM(SAL)
     ---------- ----------
        10       8750
        20      10875
        30       9400

     SQL> select deptno,job,sum(sal) from emp group by deptno,job;

     DEPTNO  JOB         SUM(SAL)
     ---------- ---------   ----------
        10   CLERK        1300
        10   MANAGER      2450
        10   PRESIDENT    5000
        20   ANALYST      6000
        20   CLERK        1900
        20   MANAGER      2975
        30   CLERK        950
        30   MANAGER      2850
        30   SALESMAN     5600

HAVING


This will work as where clause which can  be used only with group by because of absence of where clause in group by.
Ex:
     SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) > 3000;

              DEPTNO   JOB              TSAL
   ----------  ---------      ----------
        10    PRESIDENT    5000
        20    ANALYST        6000
        30    SALESMAN     5600

SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) > 3000
         order by job;

   DEPTNO    JOB          TSAL
 ----------  ---------    ----------
   20          ANALYST       6000
   10        PRESIDENT   5000
   30        SALESMAN    5600

ORDER OF EXECUTION

  • Group the rows together based on group by clause.
  • Calculate the group functions for each group.
  • Choose and eliminate the groups based on the having clause.
  • Order the groups based on the specified column.
Share this article :

2 comments:

  1. Hi There,

    Fully agree on SQL Group By And Having. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    I have written a query that essentially unions a new set of data with a previous set of data for a bridge examination.

    I need to do this for hundreds of different bridges. Each bridge is identified by a 'guid'.

    I have created one table.

    The template table contains the bridge elements and current scores assigned for the current bridge exams.
    The element table contains the historic elements associated with each bridge but no scores.
    These tables are unioned for each guid (bridge identifier) to get all thehistoric elements of each bridge (as current bridge exams may have not examined some historic elements) and then left joined back to the template table to get the current scores for each element. Any elements that have 'null' in the scores after the left joined are updated to 'ne' (not examined)

    Anyway this is just the background..

    The real question is how do i automate this..

    I know you can do a for each loop and then insert those value into the third table.. target template.

    I presume i need something along the lines of..

    for rob_rec in (select distinct guid from template)
    insert into target_template (
    MAJORELEMENTCODE,
    MAJORELEMENTNO,
    MINORELEMENTCODE,
    MINORELEMENTNO,
    ELEMENTMATERIAL,
    SEVERITYEXTENT1,
    SEVERITYEXTENT2,
    CMSEVERITYEXTENT1,
    CMSEVERITYEXTENT2,
    COMMENTS,,
    GUID
    )
    I just need to automate the process for each guid in the template table and insert the values into the target_template table.

    Thank you very much and will look for more postings from you.

    Merci,
    Preethi

    ReplyDelete
  2. Olà,

    Grazie! Grazie! Grazie! Your blog is indeed quite interesting around SQL Group By And Having. I agree with you on lot of points!

    how to get the first non space character from a string in Oracle. Could you help me on this?

    Anyways great write up, your efforts are much appreciated.


    Many Thanks,
    Radhey

    ReplyDelete