The Sum() is an aggregate function in MySQL. You can use sum query with if condition. To understand the sum query with if condition, let us create a table.The query to create a table −mysql> create table SumWithIfCondition    −> (    −> ModeOfPayment varchar(100)    −> ,    −> Amount int    −> ); Query OK, 0 rows affected (1.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SumWithIfCondition values('Offline', 10); Query OK, 1 row affected (0.21 sec) mysql> insert into SumWithIfCondition values('Online', 100); Query OK, 1 row affected ... Read More

To combine date and time column into a timestamp, you can use cast() function with concat().The syntax is as follows −select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) as anyVariableName from yourTableName;In the above concept, you will use cast() when your date and time is in string format. The cast() function can be used only for datetime. To understand the above syntax, let us create a table.The query to create a table is as follows −mysql> create table DateAndTimeToTimestamp −> ( −> Duedate date, −> DueTime time −> ); ... Read More

You can use in-built function UNIX_TIMESTAMP() from MySQL to get the timestamps and the difference between two timestamps. The syntax is as follows −SELECT UNIX_TIMESTAMP(yourColumnName1) - UNIX_TIMESTAMP(yourColumnName2) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table DifferenceInSeconds −> ( −> FirstTimestamp TIMESTAMP, −> SecondTimestamp TIMESTAMP −> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DifferenceInSeconds values('2012-12-12 ... Read More

You can create a function using create function command. The syntax is as follows −delimiter // DROP FUNCTION if exists yourFunctionName; CREATE FUNCTION yourFunctionName(Parameter1, ...N) returns type BEGIN # declaring variables; # MySQL statementns END // delimiter ;First, here we will create a table and add some records in the table. After that, a simple function will be created. The following is the query to create a table −mysql> create table ViewDemo −> ( −> Id int, −> Name varchar(200), −> Age int −> ); ... Read More

Let us first create a table to increase and adecrease row value by 1. The following is the query −mysql> create table IncrementAndDecrementValue    −> (    −> UserId int,    −> UserScores int    −> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into IncrementAndDecrementValue values(101, 20000); Query OK, 1 row affected (0.13 sec) mysql> insert into IncrementAndDecrementValue values(102, 30000); Query OK, 1 row affected (0.20 sec) mysql> insert into IncrementAndDecrementValue values(103, 40000); Query OK, 1 row affected (0.11 sec)Display all records ... Read More

You can increase and decrease row value by 1 in MySQL using UPDATE command. The syntax is as follows −UPDATE yourTableName set yourColumnName = yourColumnName-1 where condition;Let us create a table to decrease row value by 1. The following is the query −mysql> create table IncrementAndDecrementValue    −> (    −> UserId int,    −> UserScores int    −> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into IncrementAndDecrementValue values(101, 20000); Query OK, 1 row affected (0.13 sec) mysql> insert into IncrementAndDecrementValue values(102, 30000); ... Read More

You can use in-built function UPPER() from MySQL to change a lower case to upper case. The syntax is as follows with select statement.SELECT UPPER(‘yourStringValue’);The following is an example showing string in lower case −mysql> select upper('john');Here is the output displaying string in upper case −+---------------+ | upper('john') | +---------------+ | JOHN          | +---------------+ 1 row in set (0.00 sec)If you already have a table with a lower case value, then you can use the UPPER() function with update command. The syntax is as follows −UPDATE yourTableName set yourColumnName = UPPER(yourColumnName);To understand the above concept, let ... Read More

To get average on time column, use the below syntax. It will give the average in time format −SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(yourColumnName))) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query −mysql> create table AverageOnTime    −> (    −> PunchInTime time    −> ); Query OK, 0 rows affected (0.61 sec)Insert time values in the table using insert command. The query to insert records is as follows −mysql> insert into AverageOnTime values('00:00:40'); Query OK, 1 row affected (0.20 sec) mysql> insert into AverageOnTime values('00:02:50'); Query OK, 1 row affected (0.15 sec) ... Read More

To extract first word from a field, use in-built SUBSTRING_INDEX() function. The syntax is as follows −SELECT SUBSTRING_INDEX(yourColumnName, ’ ‘, 1) as anyVariableName from yourTableName;In the above query, if you use -1 in place of 1 then you will get the last word. To understand the above concept, let us create a table. The following is the query to create a table.mysql> create table FirstWordDemo −> ( −> AllWords longtext −> ); Query OK, 0 rows affected (0.83 sec)Now insert some words in the table using insert command. The query is ... Read More

To display the value of a variable, you can use select statement. The syntax is follows −SELECT @yourVariableName;Let us first create a variable. This can be done using SET command. The following is the syntax to create a variable −SET @yourVariableName = yourValue;Let us check the above syntax to create a variable and display the value of created variable.The following is the query to create a variable −mysql> set @FirstName = 'Bob'; Query OK, 0 rows affected (0.04 sec)Now you can display the value of a variable using select statement. The query is as follows −mysql> select @FirstName;The following is ... Read More