WHEN you have non-aggregate columns in SELECT , and you want to aggregate COUNT(*) function, then, you need GROUP BY. If you use only COUNT(*), then GROUP BY not needed. Check the rules below before you use it.
How to use SQL Count function
- You can use “Count function” as a Column in SELECT statement
- You cannot use “Count function” in Where clause
- You can use “Count function” with Having clause
- You need to use “Group By ” if you want to use non aggregate columns.
Count(*) means, the count value of fields in a Table. In this post, I will explain my results on how to use COUNT(*) and GROUP BY.
SQL Count Aggregate function
In this post, I am creating a Table and I will insert rows. Then I get data from the Table using Count Function.
If you do not use the SQL query correctly, you will not get the correct result.
Steps that I followed to use Count function
Step:1 to enter data into a Table
|S.NO||Student Name||Country Name|
Step: 2 To create a Table using SQL query
Step: 3 SQL query to check Table created or not
Table “desc” result
sno int(11) YES NULL
student_name varchar(20) YES NULL
country_name char(20) YES NULL
Also Read: What is aggregate function by dofactory
Step: 4 SQL Query to Insert values into a Table
insert into counttable values (1, 'Ramani', 'USA'); insert into counttable values ( 2, 'Krishan', 'Japan' ); insert into counttable values ( 3, 'Mani', 'UK' ); insert into counttable values ( 4, 'Rani', 'India' );
SQL Result after inserting values
1 Ramani USA
2 Krishan Japan
3 Mani UK
4 Rani India
Step: 5 SQL Query “Count function” with Group By and Order By
An SQL query to find a student who studied in the USA by using SQL Count Group by
select student_name, count(*) from counttable where country_name = 'USA' group by student_name order by student_name;
Group By student_name command allows for the Aggregates to be calculated per student_name.
The data has also been sorted with the ORDER BY statement.
Result of SQL Count (*)
The important point is the non-aggregate field in SELECT statement requires GROUP BY. The ORDER BY will sort the result. If you do not want sort, you no need to use in your SQL.