SQL How to Use COUNT Correctly with GROUP BY

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.NOStudent NameCountry Name
1RamaniUSA
2KrishnaJapan
3ManiUK
4RaniIndia

Step: 2 To create a Table using SQL query

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

Step: 3 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

Also Read: What is aggregate function by dofactory

SQL aggregate functions

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 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.