CODEWITHSUNDEEP
sqlmysqldatabase
vehomzzz
vehomzzz

Reputation: 44568

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

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

Upvotes: 130

Views: 282169

Answers (11)

salam_verdim_alana_panyatkasi
salam_verdim_alana_panyatkasi

Reputation: 373

GROUP BY clause provide us grouping data of each record, however order by clause provide us ordering data for ASC or DESC .

1)To understand GROUP BY clause imagine that you have a table and there are a lots of record and you want to grouping each record where each record of value is only same value grouping by some criteria e.g (AVG,SUM,COUNT,MIN,MAX etc. ) what will you do in this situation? before group by you were writing such queries;

select avg(sal), job
from emp
where job='MANAGER';

select avg(sal), job
from emp
where job='DIRECTOR';

select avg(sal), job
from emp
where job='USER';

They return avg(sal) each of people by job criteria in the above queries, however sql has a better way to provide us grouping by some criteria each of record, which has same value. So that without write more complex query like in the above you can write only one query and see result each record group by

select avg(sal), job
from emp
group by job;

This will return each same value of record of avg(sal) some group by criteria. Note GROUP BY keyword affect when used to together aggregate functions.

Upvotes: 0

Khadija
Khadija

Reputation: 41

  1. 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.
  2. 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.
  3. 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.
  4. Grouped data cannot be filtered by WHERE clause. Order data can be filtered by WHERE clause.

Upvotes: 1

Max Schmeling
Max Schmeling

Reputation: 12509

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.

Upvotes: 25

GorvGoyl
GorvGoyl

Reputation: 49150

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> 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> 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> 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 |
+---------+-------------+

Upvotes: 88

PatrikAkerstrand
PatrikAkerstrand

Reputation: 45721

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.

Upvotes: 16

John
John

Reputation: 13720

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;

Upvotes: 0

Swapnil Chincholkar
Swapnil Chincholkar

Reputation: 2909

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).

Upvotes: 8

RiddlerDev
RiddlerDev

Reputation: 7439

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) > 1

=
Peter

Upvotes: 262

Im_khan
Im_khan

Reputation: 11

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

Upvotes: 1

Cătălin Pitiș
Cătălin Pitiș

Reputation: 14341

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.

Upvotes: 4

Christian Merat
Christian Merat

Reputation: 4304

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).

Upvotes: 79

Related Questions