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.
Using the COUNT() Function
Query-1
CREATE TABLE product ( user_id INTEGER PRIMARY KEY, product_1 VARCHAR(50), product_2 VARCHAR(50), price INT ); INSERT INTO product (product_1, price) VALUES ('rice', 400); INSERT INTO product (product_2, price) VALUES ('grains', 600); SELECT COUNT(user_id) FROM product;
Output:
Query-2
Using the COUNT() function and counting float values.
CREATE TABLE floats ( user_id INTEGER PRIMARY KEY, float_val FLOAT ); INSERT INTO floats (float_val) VALUES (3.5); INSERT INTO floats (float_val) VALUES (2.1); INSERT INTO floats (float_val) VALUES (6.3); INSERT INTO floats (float_val) VALUES (9.9); INSERT INTO floats (float_val) VALUES (7.0); SELECT COUNT(*) FROM floats;
Output :
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 with GROUP BY Clause
Using the Count() Function with GROUP BY Clause
Query-5
CREATE TABLE package01 ( user_id INTEGER PRIMARY KEY AUTOINCREMENT, item VARCHAR(10), mrp INTEGER, sp INTEGER ); INSERT INTO package01 (item, mrp, sp) VALUES ('book1', 250, 240); INSERT INTO package01 (item, mrp, sp) VALUES ('book2', 350, 320); INSERT INTO package01 (item, mrp) VALUES ('book3', 400); INSERT INTO package01 (item, mrp) VALUES ('book3', 400); SELECT item, COUNT(*) FROM package01 GROUP BY item;
Output:
Contact Us