Open In App

COUNT() Function in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

Count() function in MySQL is used to find the number of indexes as returned from the query selected.

Features 

  1. This function finds the number of indexes as returned from the query selected.
  2. It comes under Numeric Functions.
  3. It accepts only one parameter namely expression.
  4. 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:

 

output1

 

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: 

output3

 

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:

output4

 

 

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:

output5

 


Last Updated : 13 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads