Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
Articles by Ankith Reddy
Page 67 of 73
Select multiple sums with MySQL query and display them in separate columns?
To select multiple sum columns with MySQL query and display them in separate columns, you need to use CASE statement. The syntax is as follows:SELECT SUM( CASE WHEN yourColumnName1=’yourValue1’ THEN yourColumnName2 END ) AS yourSeparateColumnName1, SUM( CASE WHEN yourColumnName1=’yourValue2’ THEN yourColumnName2 END ) AS yourSeparateColumnName2, SUM( CASE WHEN yourColumnName1=’yourValue3’ THEN yourColumnName2 END ) AS yourSeparateColumnName3, . . . N FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectMultipleSumDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> PlayerName varchar(20), -> PlayerScore int, ...
Read MoreHow to create JSON format with group-concat in MySQL?
You can create JSON format using group_concat() function from MySQL. The syntax is as follows −SELECT yourColumnName1, GROUP_CONCAT(CONCAT('{anytName:"', yourColumnName, '", anyName:"', yourColunName, '"}')) anyVariableName from yourTableName group by yourColumnName1;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table JsonFormatDemo -> ( -> UserId int, -> UserName varchar(100), -> UserEmail varchar(100) -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using insert ...
Read MorePopulating a table from query results in MySQL?
To populate a table from query results, use the following syntax:INSERT yourTableName(yourColumnName1, yourColumnName2, yourColumnName3, ..........N) SELECT yourColumnName1, yourColumnName2, yourColumnName3, ..........N FROM yourAnotherTableName;To understand the above syntax, let us create a table. The first table is as follows with some records. The query to create a table is as follows:mysql> create table PopulateTableDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> Amount int, -> ArrivalDateTime datetime, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.68 sec)Now you can insert some records in the table using insert command. The ...
Read MoreHow to concat Values in MySQL Query and to handle Null values as well?
You can use CONCAT() method to concatenate values while IFNULL() method is used to handle NULL values. The syntax is as follows:SELECT CONCAT('anyStringValue:', IFNULL(yourColumnName, 'anyStringValue’)) AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ConcatValues -> ( -> Id int NOT NULL AUTO_INCREMENT, -> FirstName varchar(20), -> MiddleName varchar(20), -> LastName varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert ...
Read MoreHow do I add more members to my ENUM - type column in MySQL?
You can use alter command. The syntax is as follows −ALTER TABLE yourTableName MODIFY COLUMN yourColumNam enum(yourOldValue1, yourOldValue2, ....N, yourNewValue1, yourNewValue2, ....N);To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table EnumDemo -> ( -> AllColors enum('RED', 'GREEN') -> ); Query OK, 0 rows affected (0.66 sec)Now you have two members in AllColors of enum type. If you want to add more members to your enum type, use the following query −mysql> alter table EnumDemo -> modify column ...
Read MoreHow to subtract 30 days from the current datetime in MySQL?
To subtract 30 days from current datetime, first we need to get the information about current date time, then use the now() method from MySQL. The now() gives the current date time.The method to be used for this is DATE_SUB() from MySQL. Here is the syntax to subtract 30 days from current datetime.The syntax is as follows −DATE_SUB(NOW(), INTERVAL 30 DAY);The above syntax calculates the current datetime first and in the next step, subtracts 30 days. Let us first seethe query to get the current datetime −mysql> select now();Here is the output −+---------------------+ | now() ...
Read MoreWhat is the difference between SQL and MySQL?
SQLSQL is a type of language that can be used to utilize your database. It is a base language for databases like MySQL, SQL Server, Oracle etc. SQL stands for Structure Query Language and it can be used to utilize the relational database management system.This can also be used for accessing, manipulating and updating data in the database using some commands. The SQL commands are as follows −SELECTUPDATE, etc.SQL can also be used in the creation of schema as well as controlling the data access.MySQLMySQL is a relational database management system that utilizes the SQL command. MySQL provides the tools ...
Read MoreWhich rows are returned while using LIMIT with OFFSET in MySQL?
Suppose the LIMIT is 4 and OFFSET is 6 then it will return the rows from 7 to 10 i.e. will end with row 10. The LIMIT 4 and OFFSET 6 returns row 7, 8, 9, 10.You can understand the above concept by implementing LIMIT and OFFSET. Let us create a table.mysql> create table LimitOffsettable -> ( -> Id int -> ); Query OK, 0 rows affected (0.60 sec)Let us insert some records in the table. The query is as follows −Mysql> insert into LimitOffsettable values(1); Query OK, 1 row affected ...
Read MoreMySQL Query to find tables modified in the last hour?
You can achieve this with the help of INFORMATION_SCHEMA.TABLES. Use the date_sub() with interval. The syntax is as follows −SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE DATE_SUB(NOW(), INTERVAL -1HOUR) < ‘UPDATE_TIME’;Now you can check the above syntax. Here is the query to find the tables modified in the last hour −mysql> select table_name from `INFORMATION_SCHEMA`.`TABLES` -> WHERE -> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;Output+---------------------+ | TABLE_NAME | +---------------------+ | innodb_table_stats | | innodb_index_stats | | employeeinformation | +---------------------+ 3 rows in set (0.37 sec)The above query selects only table name. If you want information like table schema, table ...
Read MoreCount number of times value appears in particular column in MySQL?
You can use aggregate function count() with group by. The syntax is as follows.select yourColumnName, count(*) as anyVariableName from yourtableName group by yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table CountSameValue -> ( -> Id int, -> Name varchar(100), -> Marks int -> ); Query OK, 0 rows affected (0.70 sec)Insert records in the table using insert command. The query is as follows.mysql> insert into CountSameValue values(1, 'Sam', 67); Query OK, 1 row affected (0.17 sec) mysql> insert into CountSameValue values(2, 'Mike', 87); Query OK, 1 ...
Read More