Skip to main content

Database: How To Use GROUP BY and ORDER BY in SQL

Structured Query Language (SQL) databases can store and manage a lot of data across numerous tables. With large data sets, it’s important to understand how to sort data, especially for analyzing result sets or organizing data for reports or external communications.

Two common statements in SQL that help with sorting your data are GROUP BY and ORDER BY.


database, group by, order by


GROUP BY:

GROUP BY is most commonly  used with SQL aggregate functions to compute statistics (such as a count of certain values, sum, average, and the minimum/maximum value in a set) for a group of rows. 

Example 1: Grouping by a single column and performing a COUNT:

Suppose we have a table named "orders" with columns "product_id" and "quantity_sold". We want to count the number of orders for each product.


Output:


This result shows the count of orders for each product.


Example 2: Using aggregate functions with GROUP BY:

Suppose we have a table named "employees" with columns "department" and "salary". We want to find the total salary expenditure for each department.



Output:


This result shows the total salary expenditure for each department.


ORDER BY:

Let’s talk about ORDER BY. This command sorts the query output in ascending (1 to 10, A to Z) or descending (10 to 1, Z to A) order. The ascending sort is the default; if you omit the ASCending or DESCending keyword, the query will be sorted in ascending order. You can specify the sort order using ASC or DESC. Here’s a simple example:


Output:



This query selects the movie name, the city where the movie is showing, and the gross earnings. Then the output is sorted by gross earnings using the ORDER BY clause.


Example 1: Ordering by a single column in ascending order:

Suppose we have a table named "students" with columns "student_id", "name", and "score". We want to retrieve the names and scores of all students and order them by their scores in ascending order.


Output:


This result shows the names and scores of all students ordered by their scores in ascending order.

Example 2: Ordering by multiple columns:

Suppose we have a table named "employees" with columns "department" and "salary". We want to retrieve the department and salary of all employees and order them first by department in ascending order and then by salary in descending order.


Output:


This result shows the department and salary of all employees ordered by department in ascending order and within each department, the salaries are ordered in descending order.


GROUP BY and ORDER BY Together:

Example 1: Grouping by a column and ordering by the aggregated value

Suppose we have a table named "employees" with columns "department" and "salary". We want to find the total salary expenditure for each department and then order the results by the total salary expenditure in descending order.

Database: Group by ,Order by

Output:



Example 2: Grouping by multiple columns and ordering by one of them

Suppose we have a table named "sales" with columns "product_name", "category", and "sales_amount". We want to find out the total sales amount for each product category in each city and then order the results by the city name.

Database: Group by ,Order by

Output:






Labels:

Comments

Post a Comment

Popular posts from this blog

Implementing Advance Query Optimization in Django ORM

 Django's ORM makes database interactions seamless, allowing developers to write queries in Python without raw SQL. However, as applications scale, inefficient queries can slow down performance, leading to high latency and database load.  This guide explores advanced query optimization techniques in Django ORM to go beyond basic CRUD (Create, Read, Update, Delete) operations and improve efficiency.  1. Use QuerySet Caching to Avoid Repeated Queries Using cache reduces redundant queries for frequently accessed data. Caching helps reduce repeated database hits. 2. Avoid .count() on Large Datasets Using .count() on large tables can be expensive Inefficient way: Optimized way ( .exists() is Faster) 3. Use Indexes for Faster Lookups Indexes speed up queries on frequently filtered fields. Add db_index=True for frequently queried fields: 4. Optimize Bulk Inserts and Updated Performing operations on multiple records one by one is inefficient. Use bulk_create() for mass insert...
Post a Comment
Read more

Django Optimization Processes for Write Operation for Postgresql

When optimizing a Django project for large write operations, especially when dealing with PostgreSQL, there are several strategies you can employ to reduce the time it takes to perform these operations: 1. Bulk Inserts In django, we create objects using create()  . Asynchronous version is acreate() .It's a  convenience method for creating an object and saving it all in one step.  and  These are same and equivalent. The create() method is used to create and save a single object in the database. Example: Instead of inserting one row at a time, consider using Django's bulk_create() method to insert multiple rows in a single query. This reduces the overhead of multiple database round trips. Example:  The bulk_create() method is used to create and save multiple objects in the database in a single query. It accepts a list of model instances and inserts them into the database in a single batch operation, which significantly reduces the overhead compared to individ...
Post a Comment
Read more

Django: Request/Response Cycle

Django Request Life Cycle  A web application or a website revolves around the request-response cycle and Django applications are no exception to this. But it is not just a two step process. Our Django applications needs to go through various stages to return the end user some result. To understand the Django framework better we must understand how the requests are initiated and the end result is served to the end user. When setting up a new Django project, one of the first things you’ll do is wire up your URLconfs and set up some views. But what’s actually happening under the hood here? How does Django route traffic to the view, and what part do middlewares play in this cycle? Layers of Django Application Request Middlewares URL Router(URL Dispatcher) Views Context Processors Template Renderers Response Middlewares Whenever a request comes in first it goes to the web server (Ngnix /Apache) . The the request goes to django's WSGI (Web Server Gateway Interface) / ASGI  (Asynchr...
Post a Comment
Read more