Thursday, September 5, 2013

About GROUP BY and ORDER BY

4:48 AM By

My Site:
Since  GROUP BY and ORDER BY Clauses have significant role on writing simple to complex level of query in the business logics. But still there are some point where people can do mistake while using GROUP BY and ORDER BY Clause.
Here , I am going to explain main thing you should know before using them.

Point you should know about GROUP BY and ORDER BY before writing SQL Query:

1. All the fields used in ORDER BY clause must be used in GROUP BY clause(if there is GROUP BY clause used).
 Example: You can not do this: ✗
 
 SELECT 
    NAME, ADDRESS
 FROM EMPLOYEE
 GROUP BY 
    NAME, ADDRESS
 ORDER BY
    NAME, ADDRESS, SALARY
 This query will fail because ORDER BY have SALARY, Which is not exist in GROUP BY.

2.  All the field used in SELECT clause must be exist in GROUP BY clause except for Aggregate Functions.
 Example:You CAN DO this:(GROUP BY can have fields more than used in select clause) ✓
 SELECT 
     NAME,ADDRESS
 FROM EMPLOYEE
 GROUP BY 
     NAME, ADDRESS, SALARY
3. Field used with Aggregate functions(SUM, MIN, MAX, AVG) are optional to the GROUP BY clause
 Example:You CAN DO this. ✓
 SELECT 
    NAME,ADDRESS, SUM(SALARY)
 FROM EMPLOYEE
 GROUP BY 
    NAME, ADDRESS
4. Field used in SELECT clause must be exist in GROUP BY Clause 
Example:You CAN NOT DO this. ✗
 SELECT 
     NAME, ADDRESS, SALARY
 FROM EMPLOYEE
 GROUP BY 
     NAME, ADDRESS
5.  We can not give Alais Name in GROUP BY clause, but we can give it to ORDER BY clause.

 a). Example: IT WORKS! ✓
    SELECT
        SUBSTR(ADDRESS, 1, 3) as ADD, NAME
    FROM
        EMPLOYEE
    GROUP BY 
        SUBSTR(ADDRESS, 1, 3), NAME
    ORDER BY
        ADD , NAME
 b). Example: IT DOES NOT WORKS!(Alias Name can not be used in GROUP BY clause) ✗
    SELECT
        SUBSTR(ADDRESS, 1, 3) as ADD, NAME
    FROM
        EMPLOYEE
    GROUP BY 
        ADD, NAME
    ORDER BY
        ADD , NAME
I am a new blogger. I like sharing the knowledge. Now I will share some useful knowledge i have earn, from my past working experience.
Please give your feedback!

Read more →