SQL creating view with count() function

View with count() function

In this page, we are discussing usage of aggregate COUNT() function along with the VIEW in a CREATE VIEW statement.

Example:

Sample table: customer

+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
..........
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  

View the table

To create a view 'noofgrade' with two columns 'grade' and 'gradecount' of the table 'customer' with following conditions -

1. 'gradecount' column created with number of grades from the customer table,

2. unique 'grade' of 'customer' table should be grouped,

the following SQL statement can be used:

SQL Code:

CREATE VIEW noofgrade(grade,gradecount)
AS SELECT grade,COUNT(*) 
FROM customer
GROUP BY grade;

Output:

To execute query on this view

SQL Code:

SELECT * FROM noofgrade;

SQL creating view with count(), sum() and avg()

In the following, to discuss the SQL VIEW we have used the aggregate function COUNT() and SUM() and AVG() with the SQL CREATE VIEW statement.

Example:

Sample table: orders

    ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
.........
    200102       2000            300 25-MAY-08 C00012          A012 

View the table

To create a view 'ordersview' from the table 'orders' with following conditions -

1. 'c_cust_code' column must be created with COUNT(DISTINCT cust_code) from the 'orders' table,

2. 'c_ag_code' column must be created with COUNT(DISTINCT agent_code) from the 'orders' table,

3. 'c_ord_num' column must be created with COUNT(ord_num) from the 'orders' table,

4. 'avg_ord_amt' column must be created with AVG(ord_amount) from the 'orders' table,

5. 'sum_amt' column must be created with SUM(ord_amount) from the 'orders' table,

6. unique 'ord_date' must be within the group,

the following SQL statement can be used:

SQL Code:

CREATE VIEW ordersview (ord_date,c_cust_code,
c_ag_code,c_ord_num,avg_ord_amt,sum_amt)
AS SELECT ord_date,COUNT(DISTINCT cust_code),
COUNT(DISTINCT agent_code),COUNT(ord_num),
AVG(ord_amount), SUM(ord_amount) 
FROM orders 
GROUP BY ord_date;

Output:

To execute query on this view

sql Code:

SELECT * FROM ordersview; 

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

PREV : Create View
NEXT :Create view with join