Count() With Where Clause
Query-3
Using the COUNT() function and getting the output where MRP is greater than the number of counts of MRP.
CREATE TABLE package ( user_id INTEGER PRIMARY KEY, item VARCHAR(10), mrp INTEGER ); INSERT INTO package (item, mrp) VALUES ('book1', 3); INSERT INTO package (item, mrp) VALUES ('book2', 350); INSERT INTO package (item, mrp) VALUES ('book3', 400); SELECT * FROM package WHERE mrp > (SELECT COUNT(mrp) FROM package);
Output:
Query-4
Using the COUNT() function and getting the records of (MRP-sales price).
CREATE TABLE package001 ( user_id INTEGER PRIMARY KEY AUTOINCREMENT, item VARCHAR(10), mrp INTEGER, sp INTEGER ); INSERT INTO package001 (item, mrp, sp) VALUES ('book1', 250, 240); INSERT INTO package001 (item, mrp, sp) VALUES ('book2', 350, 320); INSERT INTO package001 (item, mrp) VALUES ('book3', 400); SELECT COUNT(*) FROM package001 WHERE mrp - sp IS NOT NULL;
Output:
COUNT() Function in MySQL
Count() function in MySQL is used to find the number of indexes as returned from the query selected.
Features
- This function finds the number of indexes as returned from the query selected.
- It comes under Numeric Functions.
- It accepts only one parameter namely expression.
- This function ignores NULL values and doesn’t count them.
Syntax:
COUNT(expression)
- Parameter values: This method accepts only one parameter as given below:
- Expression: A specified expression can either be a field or a string-type value.
- Returns: It returns the number of indexes as returned from the query selected.