GROUP FUNCTIONS

GROUP FUNCTIONS

1. GRANULARITY

   • Granularity is the level of depth represented by the data in a fact or dimension table.
   • HIGH GRANULARITY
     • MINUTE LEVEL OF DATA AT ITS ATOMIC FORM
   • LOW GRANULARITY
     • SUMMARY VIEW OF DATA AND TRANSACTIONS

2. RULES OF GROUP FUNCTIONS

   • 1.USED IN SELECT LIST & HAVING CLAUSE
   • 2.WHERE CLAUSE IS NOT USED FOR FILTERING
   • 3.DEFAULT- WORKS ON WHOLE TABLE, OR ACCORDING TO GROUP BY CLAUSE
   • 4.DATA CAN CONTAIN DUPLICATED DATA OR YOU CAN ELIMINATE THEM AND WORK
   • 5.DISTINCT CAN BE USED IN GROUP FUNCTIONS
     • IF YOU USE DISTINCT, DUPLICATED DATA IS REMOVED FROM THE DATA ON WHICH THE GROUP FUNCTIONS ARE WORKING.
   • 6.SINGLE ROW FUNCTION CANNOT BE DECLARED WHEN WE ARE USING GROUP FUNCTIONS IN THE SELECT LIST
     • EXAMPLE: SELECT SUM(SAL), ENAME FROM EMP
   • 7.GROUP BY CLAUSE IS USED IN CASE OF SINGLE ROW FUNCTION WITH GROUP FUNCTION
     • EXAMPLE : SELECT SUM(SAL), ENAME FROM EMP GROUP BY ENAME
   • 8. WILL NOT ALLOW NULL PROPAGATION EXCEPT COUNT FUNCTION

3. SUM ( )

4. AVG ( )

5. MAX ( )

6. MIN ( )

7. COUNT ( )

   • NULL PROPAGATION IS ALLOWED IN ONLY ONE CASE
     • WHEN IT IS RECORD COUNTING NULL PROPAGATION IS ALLOWED
   • NULL PROPAGATION IS NOT ALLOWED IN ONE CASE AGAIN IN SAME COUNT FUNCTION ONLY
     • WHEN IT IS VALUE COUNTING NULL PROPAGATION IS NOT ALLOWED
   • COUNTING THE RECORDS
     • METHOD 1----SELECT COUNT(*) FROM TABLE NAME
     • METHOD 2----SELECT COUNT(1) FROM TABLE NAME
       • FAST WAY FOR COUNTING THE RECORDS
       • METADATA SEARCH NEED NOT BE DONE HERE
       • LITERAL (CONSTANT) IS USED HERE FOR SEARCHING. LITERAL REPEATS FOR EVERY RECORD COMING FROM THE DATABASE
     • METHOD 3-----SELECT SUM(1) FROM TABLE NAME

8. STDDEV ( )

9. VARIANCE ( )

10. MAX ( ), MIN ( ),------WORKS VERTICALLY ON THE COLUMNS

11. GROUP BY CLAUSE

    • CLASSIFIES OR GROUPS THE DATA
    • GROUP BY CLAUSE AND DISTINCT ARE NOT SAME
    • YOU CANNOT USE COLUMN ALIASES DIRECTLY IN THE GROUP BY CLAUSE
    • COLUMNS WHICH ARE PART OF THE GROUP BY CLAUSE SHOULD BE PART OF THE PROJECTION.
    • AS MANY COLUMNS IN THE PROJECTION SO MANY COLUMNS SHOULD BE THERE IN THE GROUP BY CLAUSE
    • COLUMNS WHICH ARE PART OF THE GROUP BY CLAUSE NEED NOT BE PART OF PROJECTION OR SELECT LIST

12. NESTING

    • THE AMOUNT OF NESTING IS MAXIMUM 2 LEVELS
    • DEFINITELY IS SHOULD CONTAIN A GROUP BY CLAUSE