Count(*) SQL result Quick analysis to improve Query

The Count(*) in SQL is an aggregate function. You can find Count value from your SQL statement.

Count means, the count value of fields you are looking from a Table. In this post , I will compare my results, by running a query.

SQL Count Aggregate function

This post tells you how to use Count(*) in SQL query correctly and why

I have a Table with few rows. After creating Table, I will insert values. Then I will use MySQL to compile SQL query.

If you do not use SQL query correctly, you will not get correct result.

Step by step we execute and improve our query after analysis.

Raw data to enter into a Table

S.NOStudent NameCountry Name

SQL Query to Create Table

create table counttable(sno integer ,student_name varchar(20),country_name char(20));

SQL query to check Table created or not

desc counttable;

Table desc result

sno int(11) YES NULL 
student_name varchar(20) YES NULL 
country_name char(20) YES NULL 

The aggregate function definition by dofactory

SQL aggregate functions

SQL to Insert values

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 

SQL Latest Trends

SQL latest trends

SQL query to get Count of a student who studied in USA

select student_name, count(*)
from counttable
where country_name = 'USA'
group by student_name
order by student_name;

Group By student_name command allow for the Aggregates to be calculated per student_name. The data has also been sorted with the ORDER BY statement.

Result of Count (*) Query

Ramani 1

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.

Related Posts


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.