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
-
Economics & Finance
Database Articles
Page 414 of 547
Count and sort rows with a single MySQL query
Let us first create a table −mysql> create table DemoTable783 ( FirstName varchar(100) ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable783 values('Adam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable783 values('Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable783 values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable783 values('Adam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable783 values('Adam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable783 values('Robert'); Query OK, 1 ...
Read MoreMySQL query to group by column and display the sum of similar values in another column
For this, use GROUP BY HAVING clause.Let us first create a table −mysql> create table DemoTable782 ( Name varchar(100), Score int ); Query OK, 0 rows affected (1.18 sec)Insert some records in the table using insert command −mysql> insert into DemoTable782 values('John', 156); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable782 values('Carol', 250); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable782 values('Bob', 140); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable782 values('John', 126); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable782 values('John', ...
Read MoreFind the count of EMPTY or NULL columns in a MySQL table?
Let us first create a table −mysql> create table DemoTable781 ( Name varchar(100) ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable781 values(''); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable781 values('Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable781 values(''); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable781 values(null); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable781 values(null); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable781 values(''); Query OK, 1 ...
Read MoreHow to display the column names from a table excluding some in MySQL?
To exclude some of the column names, use NOT IN.Let us first create a table −mysql> create table DemoTable780 ( CustomerId int, CustomerName varchar(100), CustomerAge int, CustomerCountryName varchar(100), isMarried tinyint(1) ); Query OK, 0 rows affected (0.47 sec)Here is the query to exclude result −mysql> select group_concat(column_name) from `information_schema`.`COLUMNS` m where table_schema = 'web' and table_name = 'DemoTable780' and column_name not in ('CustomerId','CustomerCountryName') group by table_schema,table_name;This will produce the following output -+------------------------------------+ | group_concat(column_name) | +------------------------------------+ | CustomerName,CustomerAge,isMarried | +------------------------------------+ 1 row in set (0.01 sec)
Read MoreIncrement column value 'ADD' with MySQL SET clause
Since the column value ‘ADD’ is already a reserved word, therefore you need to use backticks around the word ADD like `ADD`.Let us see an example and create a table −mysql> create table DemoTable779 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, `ADD` int ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable779(`ADD`) values(54); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable779(`ADD`) values(89); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable779(`ADD`) values(66); Query OK, 1 row affected ...
Read MoreSelect rows from a MySQL table and display using IN()
Let us first create a table −mysql> create table DemoTable778 ( ClientId varchar(100), ClientName varchar(100) ); Query OK, 0 rows affected (1.05 sec)Insert some records in the table using insert command −mysql> insert into DemoTable778 values('J-101', 'John'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable778 values('A-102', 'Adam'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable778 values('C-103', 'Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable778 values('D-104', 'David'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable778 values('R-105', 'Robert'); Query OK, 1 row affected ...
Read MoreMySQL query to display the column and its values using OR in WHERE statement
Let us first create a table −mysql> create table DemoTable777 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), StudentAge int ); Query OK, 0 rows affected (1.34 sec)Insert some records in the table using insert command −mysql> insert into DemoTable777(StudentName, StudentAge) values('Chris', 23); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable777(StudentName, StudentAge) values('Robert', 21); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable777(StudentName, StudentAge) values('Mike', 19); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable777(StudentName, StudentAge) values('Bob', 22); Query OK, 1 row ...
Read MoreWhy does the following error occur in MySQL: ERROR 1062 (23000): Duplicate entry?
Let’s say you have set column values as unique key and try to insert duplicate values in the table. This will lead to ERROR 1062 (23000): Duplicate entry.Let us first create a table -mysql> create table DemoTable776 ( FirstValue int, SecondValue int, unique key(FirstValue, SecondValue) ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. While inserting duplicate value, the same error arises as shown below -mysql> insert into DemoTable776 values(10, 20); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable776 values(10, 40); Query OK, 1 row affected (0.15 ...
Read MoreSelecting rows that are older than current date in MySQL?
Let’s say the current date is 2019-08-03. Now, we will see an example and create a table −mysql> create table DemoTable840(DueDate datetime); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable840 values('2019-08-9'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable840 values('2019-07-5'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable840 values('2019-08-10'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable840 values('2019-07-13'); Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select *from DemoTable840;This will produce the ...
Read MoreMySQL query to sum rows having repeated corresponding Id
To sum, you can use aggregate function SUM(). Let us first create a table −mysql> create table DemoTable850( Id int, Price int ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable850 values(1, 90); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable850 values(2, 100); Query OK, 1 row affected (0.55 sec) mysql> insert into DemoTable850 values(2, 50); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable850 values(1, 80); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable850 values(1, 60); Query OK, ...
Read More