what is the difference between GROUP BY and ORDER BY in sql

Question

When do you use which in general  Examples are highly encouraged   I am referring so MySql  but can t imagine the concept being different on another DBMS

User · Answer

They have totally different meaning and aren't really related at all.

ORDER BY allows you to sort the result set according to different criteria, such as first sort by name from a-z, then sort by the price highest to lowest.

(ORDER BY name, price DESC)

GROUP BY allows you to take your result set, group it into logical groups and then run aggregate queries on those groups. You could for instance select all employees, group them by their workplace location and calculate the average salary of all employees of each workplace location.

User · Answer

GROUP BY is used to group rows in a select  usually when aggregating rows  e g  calculating totals  averages  etc  for a set of rows with the same values for some fields    ORDER BY is used to order the rows resulted from a select statement

User · Answer

ORDER BY alters the order in which items are returned   GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns  such as SUM  COUNT  AVG  etc

User · Answer

Simple  ORDER BY orders the data and GROUP BY groups  or combines the data   ORDER BY orders the result set as per the mentioned field  by default in ascending order   Suppose you are firing a query as ORDER BY  student roll number   it will show you result in ascending order of student s roll numbers  Here  student roll number entry might occur more than once   In GROUP BY case  we use this with aggregate functions  and it groups the data as per the aggregate function  and we get the result  Here  if our query has SUM  marks  along with GROUP BY  student first name  it will show the sum of marks of students belonging to each group  where all members of a group will have the same first name

User · Answer

The difference is exactly what the name implies  a group by performs a grouping operation  and an order by sorts   If you do SELECT   FROM Customers ORDER BY Name then you get the result list sorted by the customers name   If you do SELECT IsActive  COUNT     FROM Customers GROUP BY IsActive you get a count of active and inactive customers  The group by aggregated the results based on the field you specified

User · Answer

It should be noted GROUP BY is not always necessary as  at least in PostgreSQL  and likely in other SQL variants  you can use ORDER BY with a list and you can still use ASC or DESC per column     SELECT name first  name last  dob  FROM those guys  ORDER BY name last ASC  name first ASC  dob DESC

User · Answer

GROUP BY will aggregate records by the specified column which allows you to perform aggregation functions on non-grouped columns  such as SUM  COUNT  AVG  etc      ORDER BY alters the order in which items are returned  If you do  SELECT IsActive  COUNT    FROM Customers GROUP BY IsActive  you get a count of active and inactive customers  The group by aggregated the results based on the field you specified  If you do  SELECT   FROM Customers ORDER BY Name  then you get the result list sorted by the customer   s name  If you GROUP  the results are not necessarily sorted  although in many cases they may come out in an intuitive order  that s not guaranteed by the GROUP clause     If you want your groups sorted  always use an explicitly ORDER BY after the GROUP BY  Grouped  data cannot be filtered by WHERE clause    Order data can be filtered by WHERE clause

User · Answer

ORDER BY alters the order in which items are returned   GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns  such as SUM  COUNT  AVG  etc    TABLE  ID NAME 1  Peter 2  John 3  Greg 4  Peter  SELECT   FROM TABLE ORDER BY NAME     3 Greg 2 John 1 Peter 4 Peter  SELECT Count ID   NAME FROM TABLE GROUP BY NAME     1 Greg 1 John  2 Peter  SELECT NAME FROM TABLE GROUP BY NAME HAVING Count ID   gt  1    Peter

User · Answer

ORDER BY  sort the data in ascending or descending order     Consider the CUSTOMERS table    ---- ---------- ----- ----------- ----------    ID   NAME       AGE   ADDRESS     SALARY      ---- ---------- ----- ----------- ----------     1   Ramesh      32   Ahmedabad    2000 00      2   Khilan      25   Delhi        1500 00      3   kaushik     23   Kota         2000 00      4   Chaitali    25   Mumbai       6500 00      5   Hardik      27   Bhopal       8500 00      6   Komal       22   MP           4500 00      7   Muffy       24   Indore      10000 00    ---- ---------- ----- ----------- ----------    Following is an example  which would sort the result in ascending order by NAME   SQL gt  SELECT   FROM CUSTOMERS      ORDER BY NAME    This would produce the following result    ---- ---------- ----- ----------- ----------    ID   NAME       AGE   ADDRESS     SALARY      ---- ---------- ----- ----------- ----------     4   Chaitali    25   Mumbai       6500 00      5   Hardik      27   Bhopal       8500 00      3   kaushik     23   Kota         2000 00      2   Khilan      25   Delhi        1500 00      6   Komal       22   MP           4500 00      7   Muffy       24   Indore      10000 00      1   Ramesh      32   Ahmedabad    2000 00    ---- ---------- ----- ----------- ----------    GROUP BY  arrange identical data into groups   Now  CUSTOMERS table has the following records with duplicate names    ---- ---------- ----- ----------- ----------    ID   NAME       AGE   ADDRESS     SALARY      ---- ---------- ----- ----------- ----------     1   Ramesh      32   Ahmedabad    2000 00      2   Ramesh      25   Delhi        1500 00      3   kaushik     23   Kota         2000 00      4   kaushik     25   Mumbai       6500 00      5   Hardik      27   Bhopal       8500 00      6   Komal       22   MP           4500 00      7   Muffy       24   Indore      10000 00    ---- ---------- ----- ----------- ----------    if you want to group identical names into single name  then GROUP BY query would be as follows   SQL gt  SELECT   FROM CUSTOMERS      GROUP BY NAME    This would produce the following result   for identical names it would pick the last one and finally sort the column in ascending order        ---- ---------- ----- ----------- ----------           ID   NAME       AGE   ADDRESS     SALARY          ---- ---------- ----- ----------- ----------         5   Hardik      27   Bhopal       8500 00          4   kaushik     25   Mumbai       6500 00          6   Komal       22   MP           4500 00          7   Muffy       24   Indore      10000 00          2   Ramesh      25   Delhi        1500 00        ---- ---------- ----- ----------- ----------    as you have inferred that it is of no use without SQL functions like sum avg etc        so go through this definition to understand the proper use of GROUP BY      A GROUP BY clause works on the rows returned by a query by summarizing   identical rows into a single distinct group and returns a single row   with the summary for each group  by using appropriate Aggregate   function in the SELECT list  like COUNT    SUM    MIN    MAX    AVG      etc    Now  if you want to know the total amount of salary on each customer name   then GROUP BY query would be as follows   SQL gt  SELECT NAME  SUM SALARY  FROM CUSTOMERS      GROUP BY NAME    This would produce the following result   sum of the salaries of identical names and sort the NAME column after removing identical names    --------- -------------    NAME      SUM SALARY     --------- -------------    Hardik        8500 00     kaushik       8500 00     Komal         4500 00     Muffy        10000 00     Ramesh        3500 00    --------- -------------

User · Answer

ORDER BY shows a field in ascending or descending order  While GROUP BY shows same fieldnames  id s etc in only one output

[sql] what is the difference between GROUP BY and ORDER BY in sql

Examples related to sql

Examples related to mysql

Examples related to database