C) Oracle GROUP BY with an expression example
The following example groups the orders by year and returns the number of orders per year.
| SELECT EXTRACT ( YEAR FROM order_date) YEAR , COUNT ( order_id ) FROM orders GROUP BY EXTRACT ( YEAR FROM order_date) ORDER BY YEAR ; |
In this example, we used the EXTRACT() function to get the year information from the order’s dates.
Unlike the previous examples, we used an expression that returns the year in the GROUP BY clause.
The following picture illustrates the result:
C) Oracle GROUP BY with an expression example
The following example groups the orders by year and returns the number of orders per year.
C) Oracle GROUP BY with an expression example
The following example groups the orders by year and returns the number of orders per year.
| SELECT EXTRACT ( YEAR FROM order_date) YEAR , COUNT ( order_id ) FROM orders GROUP BY EXTRACT ( YEAR FROM order_date) ORDER BY YEAR ; |
In this example, we used the EXTRACT() function to get the year information from the order’s dates.
Unlike the previous examples, we used an expression that returns the year in the GROUP BY clause.
The following picture illustrates the result:
The Ultimate Guide to Oracle GROUP BY with Examples
with the customers table as follows: 1 2 3 4 5 6 7 8 9 10 11 SELECT name, COUNT( order_id ) FROM orders INNER JOIN customers USING(customer_id) GROUP BY name ORDER BY name; Here is the result: <span>C) Oracle GROUP BY with an expression example The following example groups the orders by year and returns the number of orders per year. 1 2 3 4 5 6 7 8 9 SELECT EXTRACT(YEAR FROM order_date) YEAR, COUNT( order_id ) FROM orders GROUP BY EXTRACT(YEAR FROM order_date) ORDER BY YEAR; In this example, we used the EXTRACT() function to get the year information from the order’s dates. Unlike the previous examples, we used an expression that returns the year in the GROUP BY clause. The following picture illustrates the result: D) Oracle GROUP BY with WHERE clause example This example uses the GROUP BY clause with a WHERE clause to return the number of shipped orders for every customer: 1 2 3 4 5 6 7 8 9 10 11
Summary
| status | not learned | measured difficulty | 37% [default] | last interval [days] | |||
|---|---|---|---|---|---|---|---|
| repetition number in this series | 0 | memorised on | scheduled repetition | ||||
| scheduled repetition interval | last repetition or drill |