PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
what is mysql Aggregate function?
Count()
The COUNT() function is used to returns the number of records returned by a query. NULL values are not counted.
It count all rows or only some rows of the table that matches a specified condition. it returns 0 if it does not find any matching rows.
We can use the count function in three types :-
COUNT(*) :- This function uses the SELECT statement to returns the count of rows in a result set.
The result set contains all Non-Null, Null, and duplicates rows.
COUNT(expression) Function: This function returns the result set without containing Null rows as the result of an expression.
COUNT(distinct expression) Function: This function returns the count of distinct rows without containing NULL values as the result of the expression.
Syntax
File Name :
SELECT COUNT (aggregate_expression)
FROM table_name
[WHERE conditions];
aggregate_expression: It specifies the column or expression whose NON-NULL values will be counted.
table_name: It specifies the tables from where you want to retrieve records.
WHERE conditions: It specifies the conditions.
File Name :
SELECT COUNT(user_name) FROM Users;
File Name :
SELECT COUNT(*) FROM users WHERE age>18;
SELECT COUNT(*) as total FROM users WHERE age>18;
count distinct
it counts the Non-Null and distinct rows in the column age:
File Name :
SELECT COUNT(DISTINCT age) FROM users;
Count() with GROUP BY :-
it returns the count of the element in each group.
SELECT name, city, COUNT(*) FROM users GROUP BY city;
Count() with HAVING and ORDER BY Clause
the users name who has at least two age same and sorts them based on the count result:
File Name :
SELECT name, age, COUNT(*) as total FROM users
GROUP BY age
HAVING COUNT(*)>=2
ORDER BY COUNT(*);
File Name :
File Name :
File Name :
File Name :