Count Items in a MySQL Table with ENUM Type

Counting ENUM items in MySQL

In MySQL, ENUM is a data type where it stores the predefined and fixed values making them ideal for categories such as sizes, grades, and statuses. Knowing the frequency of each value inside ENUM columns will allow you to make data-driven decisions. Below, we'll learn how to use MySQL's GROUP BY and COUNT() functions to get the count of ENUM values.

• GROUP BY(): It is used for aggregating data, as it follows you to perform calculations, such as totals, and averages. You can use COUNT, SUM, AVG, etc., functions on the grouped column. You can think of GROUP BY as a way to sort data into groups based on unique values in a column.

• COUNT(): MySQL's COUNT() function counts the non-NULL values in a particular column. If the specified row(s) don't exist in the table, this function returns 0. If we invoke this function as COUNT(*) this function returns the number of records in the specified table irrespective of the NULL values. It calculates a frequency of occurrence, so you can see how many times a particular number appears within a dataset. It is usually used in combination with GROUP BY.

Syntax

Syntax for counting items with type ENUM in MySQL

SELECT column_name, COUNT(*) AS alias_name
FROM table_name
GROUP BY column_name;

Example

To understand the above syntax let us start by creating a table with 'Size' column that has ENUM data type and populate it with sample data. The query is as follows:

CREATE TABLE DemoTable ( 
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Size ENUM('S','M','L','XL')
);

This command creates a table called DemoTable with an ID column, and a size column of ENUM data type with the values 'S', 'M', 'L', 'XL'.

Insert few records in the table using INSERT command ?

INSERT INTO DemoTable(Size) 
values('L'), ('S'), ('S'), ('M'), ('XL'), ('M'), ('M'), ('M'), ('XL');

To see the inserted data, view the table using SELECT command.

SELECT * FROM DemoTable;

Following is the output of the above query ?

Following is the query to count how many times each size appears using GROUP BY and COUNT() -

SELECT tbl.Size, COUNT(*) AS Frequency
FROM DemoTable tbl
GROUP BY tbl.Size;

In the above code, we have used 'tbl' for the abbreviation of DemoTable because, with tbl being an abbreviation for DemoTable, allows us to reference the table more compactly within the query. This is useful for complex queries and makes the code more readable. Following is the output of the above query ?

Conclusion

Counting ENUM values in MySQL tables with GROUP BY and COUNT functions, allows us to find insights in the data. For example, to track the product sizes, Grades, Categories, and Departments so by using this technique we can analyze data efficiently.