Oracle

The GROUP BY clause in Oracle SQL is used to group rows that have the same values in specified columns into summary rows. This is often combined with aggregate functions such as COUNT(), SUM(), or AVG() to perform calculations on each group. Using GROUP BY allows you to analyze data and gain insights from it, making it essential for reporting and data analysis.

Key Points about GROUP BY

1. Basic Usage

   • The GROUP BY clause comes after the WHERE clause and before the ORDER BY clause in a SQL statement.
   • It can group by one or more columns.

2. Example Query

   SELECT author_id, COUNT(book_id) AS total_books
   FROM books
   GROUP BY author_id;
   

   • In this example, the query counts the total number of books for each author.

3. Using Aggregate Functions

   • Common aggregate functions include:
     • COUNT(): Counts the number of rows in each group.
     • SUM(): Adds up values in a specified column.
     • AVG(): Calculates the average of values in a specified column.
   • Example:

   SELECT membership_type, AVG(rental_fee) AS average_fee
   FROM rentals
   GROUP BY membership_type;
   

4. Filtering Groups with HAVING

   • The HAVING clause can be used to filter groups based on aggregate values.
   • Example:

   SELECT author_id, COUNT(book_id) AS total_books
   FROM books
   GROUP BY author_id
   HAVING COUNT(book_id) > 5;
   

   • This query returns only authors with more than five books.

5. Multiple Columns in GROUP BY

   • You can group by multiple columns to create more granular summaries.
   • Example:

   SELECT author_id, membership_type, COUNT(rental_id) AS rentals_count
   FROM rentals
   GROUP BY author_id, membership_type;
   

Best Practices

1. Always Include Aggregate Functions: When using GROUP BY, ensure that all selected columns either appear in the GROUP BY clause or are aggregated.
2. Use HAVING Wisely: Use HAVING only when necessary to filter grouped data, as it can impact performance.
3. Keep Queries Simple: Simplify complex queries by breaking them into smaller parts when possible for better readability and maintenance.

Using GROUP BY effectively helps you extract meaningful insights from your data, allowing for better decision-making and reporting.

To gain complete access, login with gmail or outlook, no need of signup. click here

TEST CODE

In Oracle, to calculate the total count of clients in each state, you can use the following query:

SELECT state, COUNT(client_id)
FROM org_client
GROUP BY state;

Try it now

In Oracle, to determine the aggregate salary disbursement by each department, you can use the following query:

SELECT department, SUM(salary)
FROM org_employee
GROUP BY department;

Try it now

In Oracle, to identify the year of birth of the youngest client from each state, you can use the following query:

SELECT state, MAX(birth_year)
FROM org_client
GROUP BY state;

Try it now

In Oracle, to calculate the number of orders for each day, you can use the following query:

SELECT TO_CHAR(order_date, 'DD/MM/YYYY') AS order_date,
       COUNT(order_number) AS order_count
FROM act_order
GROUP BY TO_CHAR(order_date, 'DD/MM/YYYY');

Try it now

In Oracle, to tally the orders handled by each employee or sales agent, you can use the following query:

SELECT b.employee_number,
       COUNT(a.order_number) AS order_count
FROM act_order a
INNER JOIN org_employee b ON b.employee_id = a.sales_agent_employee_id
GROUP BY b.employee_number;

Try it now

In Oracle, to get the number of female clients in each city, you can use the following query:

SELECT city, COUNT(client_id) AS client_count
FROM org_client
WHERE gender = 'F'
GROUP BY city;

Try it now

Example 1:

In the following example, we will demonstrate the application of WHERE, GROUP BY, and HAVING clauses. It's important to note that the WHERE clause is used before GROUP BY, and the HAVING clause follows GROUP BY. Also, the HAVING clause cannot be used without preceding it with GROUP BY.

Example 1 - Raw data from client table

GROUP BY query

In this example, we will ascertain the number of married female clients in each city, and then identify cities that have more than one married female client.

SELECT city ,
count(client_id)
FROM org_client
WHERE gender= 'F'
AND married_flag = 1
GROUP BY city
HAVING count(client_id) > 1

Step 1, Apply WHERE conditions

In the image above, the cells highlighted with a green background and white font represent those that satisfy both criteria of the WHERE condition (being married females).

Step 1 output after applying WHERE condition

Step 2 - Apply GROUP BY

In the provided image, we need to group the data by the 'city' column. As observed, Albany is represented by 2 client rows, while the other cities each have only one row.

Step 2 output, after applying GROUP BY

Step 3 - Apply HAVING Clause

Using the HAVING clause essentially means filtering the grouped rows based on the condition specified in the HAVING clause. In this example, we are looking for groups with a client count greater than one. The cells highlighted in green with white font are those that meet the condition set by the HAVING clause.

Final output of entire query