SQL How to Use COUNT Correctly with GROUP BY

In the SELECT query, if non-aggregate columns are present and you want to use the COUNT(*) function, then, you need to use GROUP BY. However, If you want to use only COUNT(*), then GROUP BY is not needed. Here are the rules to use the count(*).

How to use the SQL Count function

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 Aggregate function

Here I create 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 to use the Count function

Step:1 to enter data into a Table

Step: 2 To create a Table using SQL query

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

Step: 3 Check if the Table is 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 

Also Read: What is an 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 (*)

Ramani 1

The non-aggregate field in the SELECT statement needs GROUP BY. The ORDER BY sorts the result. But, If you don’t want to sort, the ORDER BY is not needed.

Related Posts