Aggregate function and alias column name : Aggregate Functions Basics « Aggregate Functions « Oracle PL / SQL






Aggregate function and alias column name

 
SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25),
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE);

Table created.

SQL>
SQL> INSERT INTO product VALUES ('Small Widget', 99, 1, '15-JAN-03');

1 row created.

SQL> INSERT INTO product VALUES ('Medium Widget', 75, 1000, '15-JAN-02');

1 row created.

SQL> INSERT INTO product VALUES ('Product Number', 50, 100, '15-JAN-03');
 
1 row created.

SQL> INSERT INTO product VALUES ('Round Church Station', 25, 10000, null);

1 row created.

SQL>
SQL>
SQL>
SQL> SELECT SUBSTR(product_name, 1, 15) "Product",
  2         SUM(product_price) "Total Sold",
  3         AVG(product_price) "Average",
  4         COUNT(product_price) "Transactions",
  5         MIN(product_price) "Fewest",
  6         MAX(product_price) "Most"
  7  FROM   product
  8  GROUP BY product_name
  9  HAVING SUM(product_price) >= 5;

Product         Total Sold    Average Transactions     Fewest       Most
--------------- ---------- ---------- ------------ ---------- ----------
Product Number          50         50            1         50         50
Medium Widget           75         75            1         75         75
Round Chrome Sn         25         25            1         25         25
Small Widget            99         99            1         99         99

SQL>
SQL>
SQL> DROP TABLE product;

Table dropped.

SQL>
SQL>

 








Related examples in the same category

1.Use the aggregate functions with any valid expression
2.Order by the result of aggregate function
3.In Oracle, nulls in calculations involving aggregate functions are ignored
4.Limit the rows passed to the aggregate function using a WHERE clause
5.Combine the max and avg function together
6.Retrieves the city and average salary of the employees grouped by city
7.Having with aggregate function