The COUNT() function is an aggregate function that returns the number of rows in a table. The COUNT() function allows you to count all rows or only rows that match a specified condition.
The COUNT() function has three forms: COUNT(*), COUNT(expression) and COUNT(DISTINCT expression).
COUNT(*) function
The COUNT(*) function returns the number of rows in a result set returned by a SELECT statement. The COUNT(*) returns the number of rows including duplicate, non-NULL and NULL rows.
SELECT COUNT(*) FROM emp;
COUNT(expression)
The COUNT(expression) returns the number of rows that do not contain NULL values as the result of the expression.
SELECT COUNT(name) FROM emp;
COUNT(DISTINCT expression)
The COUNT(DISTINCT expression) returns the number of distinct rows that do not contain NULL values as the result of the expression.
SELECT COUNT(DISTINCT name) FROM emp;
SELECT COUNT(DISTINCT name,sal) FROM emp;
The return type of the COUNT() function is BIGINT. The COUNT() function returns 0 if there is no matching row found.
SELECT COUNT(*) FROM emp WHERE name LIKE '%the%';
---------------
Setting environment for using XAMPP for Windows.
Mounika@MOUNIKA-PC c:\xampp
# ram.cmd
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use vlrinst;
Database changed
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 10 | venkat | kphb | 35 | 30000 |
| 11 | praveen | pune | 28 | 35000 |
| 12 | mounika | hyderabad | 22 | 39000 |
| 13 | revathi | jntu | 26 | 20000 |
| 14 | anji | gutta | 28 | 20000 |
| 15 | harika | lb nagar | 26 | 30100 |
| 16 | praveen | jntu | 33 | 100 |
| 17 | pandu | munipeda | 35 | 30300 |
| 18 | pandu | pune | 23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.112 sec)
MariaDB [vlrinst]> select count(*) from emp;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.051 sec)
MariaDB [vlrinst]> select count(loc) from emp;
+------------+
| count(loc) |
+------------+
| 9 |
+------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select count(name) from emp;
+-------------+
| count(name) |
+-------------+
| 9 |
+-------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select count(distinct name) from emp;
+----------------------+
| count(distinct name) |
+----------------------+
| 7 |
+----------------------+
1 row in set (0.045 sec)
MariaDB [vlrinst]> select count(distinct name,sal) from emp;
+--------------------------+
| count(distinct name,sal) |
+--------------------------+
| 9 |
+--------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select count(distinct name,loc) from emp;
+--------------------------+
| count(distinct name,loc) |
+--------------------------+
| 9 |
+--------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 10 | venkat | kphb | 35 | 30000 |
| 11 | praveen | pune | 28 | 35000 |
| 12 | mounika | hyderabad | 22 | 39000 |
| 13 | revathi | jntu | 26 | 20000 |
| 14 | anji | gutta | 28 | 20000 |
| 15 | harika | lb nagar | 26 | 30100 |
| 16 | praveen | jntu | 33 | 100 |
| 17 | pandu | munipeda | 35 | 30300 |
| 18 | pandu | pune | 23 | 39300 |
+----+---------+-----------+------+-------+
9 rows in set (0.001 sec)
MariaDB [vlrinst]> insert into emp values("pandu","pune",18,50000);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [vlrinst]> insert into emp values(17,"pandu","pune",18,50000);
ERROR 1062 (23000): Duplicate entry '17' for key 'PRIMARY'
MariaDB [vlrinst]> insert into emp values(27,"pandu","pune",18,50000);
Query OK, 1 row affected (0.117 sec)
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 10 | venkat | kphb | 35 | 30000 |
| 11 | praveen | pune | 28 | 35000 |
| 12 | mounika | hyderabad | 22 | 39000 |
| 13 | revathi | jntu | 26 | 20000 |
| 14 | anji | gutta | 28 | 20000 |
| 15 | harika | lb nagar | 26 | 30100 |
| 16 | praveen | jntu | 33 | 100 |
| 17 | pandu | munipeda | 35 | 30300 |
| 18 | pandu | pune | 23 | 39300 |
| 27 | pandu | pune | 18 | 50000 |
+----+---------+-----------+------+-------+
10 rows in set (0.001 sec)
MariaDB [vlrinst]> select count(distinct name,loc) from emp;
+--------------------------+
| count(distinct name,loc) |
+--------------------------+
| 9 |
+--------------------------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select count(*) from emp where like "%i%";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'like "%i%"' at line 1
MariaDB [vlrinst]> select count(*) from emp where name like "%i%";
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select count(*) from emp where name like "kphb";
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select count(*) from emp where loc like "kphb";
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.001 sec)
MariaDB [vlrinst]> select count(*) from emp where loc = "kphb";
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.002 sec)
MariaDB [vlrinst]> select count(*) from emp where age >30;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.038 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.