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:
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:
To execute query on this view
sql Code:
SELECT * FROM ordersview;
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Create View
Next: Create view with join
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics