w3resource

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


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:

Sql creating view with count() function

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


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:

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

To execute query on this view

sql Code:

SELECT * FROM ordersview; 

See our Model Database

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Create View
Next: Create view with join