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

WSGI vs ASGI: What Every Django Developer Should Know !

  If you've been developing with Django, you've probably come across WSGI (Web Server Gateway Interface), the trusted friend of all traditional, synchronous web apps. But in this fast-moving, real-time world, you may have also heard about its dynamic, asynchronous cousin ASGI (Asynchronous Server Gateway Interface). WSGI (Web Server Gateway Interface): 1. The OG (original) Django interface, designed for synchronous HTTP requests. 2. Perfect for blogs, CMS, e-commerce, and standard web apps. 3. Uses servers like Gunicorn or uWSGI. 4. Limited to handling one request at a time. ASGI (Asynchronous Server Gateway Interface): 1. The modern, scalable interface designed for asynchronous web apps. 2. Ideal for handling WebSockets, HTTP/2, and real-time features like chat apps. 3. Built for high concurrency; uses Uvicorn, Daphne, or similar ASGI servers. 4. Allows you to leverage Python’s async and await for non-blocking code. When to Choose What: WSGI: Traditional apps where synchronou...
Post a Comment
Read more

Django pk vs id

 Django pk VS id If you don’t specify primary_key=True for any fields in your model, Django will automatically add an IntegerField to hold the primary key, so you don’t need to set primary_key=True on any of your fields unless you want to override the default primary-key behavior. The primary key field is read-only. If you change the value of the primary key on an existing object and then save it, a new object will be created alongside the old one Example: class UserProfile ( models . Model ): name = models . CharField ( max_length = 500 ) email = models . EmailField ( primary_key = True ) def __str__ ( self ): return self . name suppose we have this model. In this model we have make email field as primary key. now django default primary key id field will be gone. It'll remove from database. we can not query as   UserProfile.objects.get(id=1) after make email as primary key this query will throw an error.  Now we have to use pk  Us...
Post a Comment
Read more

How Django stores passwords

  Django Password Django provides a flexible password storage system and uses PBKDF2 by default. Django saves the password as below. <algorithm>$<iterations>$<salt>$<hash> example of a Hashed password stored in database: pbkdf2_sha256$390000$LCm33kvO7rbjbZhwJA90Sf$xfuGOzl/MJyUxqWNhsNdSThaQUvn1EjEfxZ48HA8HF4= Those are the components used for storing a User’s password,separated by the dollar-sign character and consist of:  1. The hashing algorithm 2. The number of algorithm iterations (work factor) 3. The random salt 4. The resulting password hash.  Most password hashes include a salt along with their password hash in order to protect against rainbow table attacks. Example of Making Hashed password: Here’s a simplified overview of how Django handles password storage: 1. Password Creation or Change : # When someone creates a new account or decides to change their password, Django takes their chosen password and performs a process called hashing. Has...
Post a Comment
Read more