MySQL INSERT rows with GROUP BY

INSERT rows with GROUP BY

In this page, we have discussed how to insert values into a table using MySQL INSERT INTO statement, when the column names and values are collected from another identical table using MySQL SELECT and GROUP BY.

This way you can insert the rows of one table into another identical table for a specific group.

Example

Sample table: purchase

Code:

-- This SQL statement inserts data into the table testpurchase from the table purchase
INSERT INTO testpurchase 
-- Selecting all columns from the table purchase
SELECT * 
-- Grouping the selected rows by the column cate_id
FROM purchase 
GROUP BY cate_id;

Explanation:

• The purpose of this SQL code is to copy data from the purchase table into the testpurchase table while grouping the data by the cate_id column.


• INSERT INTO testpurchase: This line specifies the target table where the data will be inserted, which is testpurchase.


• SELECT * FROM purchase: This line selects all columns from the purchase table.


• GROUP BY cate_id: This line groups the selected rows by the cate_id column. When grouping data, rows with the same value in the cate_id column will be aggregated together.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

The above statement has performed the following -

1. the rows of 'purchase' table have grouped according to the 'cate_id',
2. and inserted into the table 'testpurchase'.

To see some specific columns from inserted rows here is the code below -

-- This SQL statement selects specific columns from the table testpurchase
SELECT  invoice_no, ord_no, book_name 
-- From the table testpurchase
FROM testpurchase;

Explanation:

• The purpose of this SQL code is to select specific columns (invoice_no, ord_no, and book_name) from the testpurchase table.


• SELECT invoice_no, ord_no, book_name: This line specifies the columns that will be selected from the testpurchase table. Only the invoice_no, ord_no, and book_name columns will be included in the result set.


• FROM testpurchase: This line specifies the source table from which the columns are being selected, which is testpurchase.

Output:

mysql> SELECT  invoice_no,ord_no,book_name 
    -> FROM testpurchase;
+------------+----------------+---------------------------------+
| invoice_no | ord_no         | book_name                       |
+------------+----------------+---------------------------------+
| INV0001    | ORD/08-09/0001 | Introduction to Electrodynamics | 
| INV0002    | ORD/08-09/0002 | Transfer  of Heat and Mass      | 
| INV0006    | ORD/07-08/0007 | Guide to Networking             | 
+------------+----------------+---------------------------------+
3 rows in set (0.00 sec)

MySQL INSERT records with ORDER BY

INSERT records with ORDER BY

Here in the following, we have discussed how to insert values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT, GROUP BY, and ORDER BY.

This way you can insert the rows of one table into another identical table when columns are sorted by a specific column.

Example

Sample table: purchase

Code:

-- This SQL statement inserts data into the table testpurchase from the table purchase
INSERT INTO testpurchase 
-- Selecting all columns from the table purchase
SELECT * 
-- Grouping the selected rows by the column cate_id
FROM purchase 
-- Ordering the grouped data by the cate_id column
GROUP BY cate_id 
-- Sorting the result set by the cate_id column
ORDER BY cate_id;

Explanation:

• The purpose of this SQL code is to copy data from the purchase table into the testpurchase table while grouping the data by the cate_id column and sorting the result set by the same column.


• INSERT INTO testpurchase: This line specifies the target table where the data will be inserted, which is testpurchase.


• SELECT * FROM purchase: This line selects all columns from the purchase table.


• GROUP BY cate_id: This line groups the selected rows by the cate_id column. When grouping data, rows with the same value in the cate_id column will be aggregated together.


• ORDER BY cate_id: This line sorts the result set by the cate_id column in ascending order. This ensures that the data is ordered according to the cate_id.

Relational Algebra Expression:

Relational Algebra Tree:

Explanation:

The above statement has performed the following -

1. the rows of 'purchase' table have grouped according to the 'cate_id',
2. the group of 'purchase' table has arranged in ascending order
3. and inserted into the table 'testpurchase'.

To see some specific columns from inserted rows here is the code below -

-- This SQL statement selects specific columns from the table testpurchase
SELECT  cate_id, ord_no, book_name 
-- From the table testpurchase
FROM testpurchase;

Explanation:

• The purpose of this SQL code is to select specific columns (cate_id, ord_no, and book_name) from the testpurchase table.


• SELECT cate_id, ord_no, book_name: This line specifies the columns that will be selected from the testpurchase table. Only the cate_id, ord_no, and book_name columns will be included in the result set.


• FROM testpurchase: This line specifies the source table from which the columns are being selected, which is testpurchase.

Output:

mysql> SELECT  cate_id,ord_no,book_name FROM testpurchase;
+---------+----------------+---------------------------------+
| cate_id | ord_no         | book_name                       |
+---------+----------------+---------------------------------+
| CA001   | ORD/08-09/0001 | Introduction to Electrodynamics | 
| CA002   | ORD/08-09/0002 | Transfer  of Heat and Mass      | 
| CA003   | ORD/07-08/0007 | Guide to Networking             | 
+---------+----------------+---------------------------------+
3 rows in set (0.00 sec)

Previous: MySQL INSERT
Next: INSERT with LEFT JOIN