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

Use SET to decrease the value and WHERE to set the condition for a specific record to be 0. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Number int ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command &minusmysql> insert into DemoTable(Number) values(10); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Number) values(20); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Number) values(1); Query OK, 1 row affected ... Read More

You can use group_concat() along with concat() to concatenate all values of a single column. Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(20) ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FirstName) values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(FirstName) values('Larry'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(FirstName) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(FirstName) values('Robert'); ... Read More

To fetch domain name by passing name in MySQL, you can use substring_index(). Let us first create a table −mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserMailId varchar(200) ); Query OK,  0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(UserMailId) values('John9989@facebook.com'); Query OK,  1 row affected (0.18 sec) mysql> insert into DemoTable(UserMailId) values('983773CS@yahoo.com'); Query OK,  1 row affected (0.23 sec) mysql> insert into DemoTable(UserMailId) values('Chris95@gmail.com'); Query OK,  1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------+-----------------------+ | UserId | UserMailId | +--------+-----------------------+ | ... Read More

Use aggregate function SUM() along with OVER. Let us first create a table −mysql> create table DemoTable    (    CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    CustomerValue int    ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(CustomerValue) values(10); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(CustomerValue) values(20); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(CustomerValue) values(30); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(CustomerValue) values(40); Query OK, 1 row affected (0.14 sec)Display all records ... Read More

Let us first create a table −mysql> create table DemoTable ( UserId int, UserName varchar(20), UserAge int ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(UserId,UserName,UserAge) values(100,'John',25),(101,'Larry',24),(102,'Chris',22),(103,'Carol',27); Query OK, 4 rows affected (0.16 sec) Records: 4 Duplicates: 0 Warnings: 0Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------+----------+---------+ | UserId | UserName | UserAge | +--------+----------+---------+ | 100 | John | 25 | | 101 | Larry | 24 | | 102 | Chris | 22 | | 103 | Carol | 27 | +--------+----------+---------+ 4 rows in set (0.00 sec)

To get possible values for set field, you can use below syntax −desc yourTableName yourSetColumnName;Let us first create a table −mysql> create table DemoTable    (    Game set('Chess','Pig Dice','29 Card')    ); Query OK, 0 rows affected (0.60 sec)Following is the query to get available values for set field −mysql> desc DemoTable Game;This will produce the following output −+-------+-----------------------------------+------+-----+---------+-------+ | Field | Type                              | Null | Key | Default | Extra | +-------+-----------------------------------+------+-----+---------+-------+ | Game  | set('Chess','Pig Dice','29 Card') | YES  |     | NULL    |       | +-------+-----------------------------------+------+-----+---------+-------+ 1 row in set (0.02 sec)

You can change the MySQL date format with a specific format using DATE_FORMAT(). Following is the syntax −select date_format(yourColumnName, yourFormatSpecifier) from yourTableName;Let us first create a table −mysql> create table DemoTable    (    ShippingDate date    ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2016-01-21'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('2018-05-24'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('2019-12-31'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> ... Read More

To set a pecific date format, you need to use DATE_FORMAT() in MySQL. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ArrivalDate date ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ArrivalDate) values('2019-01-31'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(ArrivalDate) values('2019-04-26'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ArrivalDate) values('2019-03-01'); Query OK, 1 row affected (0.13 sec)Display all ... Read More

Yes, you can include a string value with spaces in ENUM type. This is particularly useful when we add descriptive options that contain multiple words. Let us explore how to create, describe, and use ENUM columns containing spaces, with supporting examples. ENUM vs VARCHAR: While both ENUM and VARCHAR can store strings with spaces, ENUM has an advantage: limiting of the number of entries that can be added into a column to a fixed set. Because of this, it is useful in situations where the values are limited and static, such as size categories, and status codes. Whereas VARCHAR is ... Read More