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:
Last Updated :
13 Apr, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...