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 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.