INSERT rows with GROUP BY

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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

+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| invoice_no | invoice_dt | ord_no         | ord_date   | receive_dt | book_id | book_name                       | pub_lang | cate_id | receive_qty | purch_price | total_cost |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| INV0001    | 2008-07-15 | ORD/08-09/0001 | 2008-07-06 | 2008-07-19 | BK001   | Introduction to Electrodynamics | English  | CA001   |          15 |       75.00 |    1125.00 |
| INV0002    | 2008-08-25 | ORD/08-09/0002 | 2008-08-09 | 2008-08-28 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |           8 |       55.00 |     440.00 |
| INV0003    | 2008-09-20 | ORD/08-09/0003 | 2008-09-15 | 2008-09-23 | BK005   | Conceptual Physics              | NULL     | CA001   |          20 |       20.00 |     400.00 |
| INV0004    | 2007-08-30 | ORD/07-08/0005 | 2007-08-22 | 2007-08-30 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |          15 |       35.00 |     525.00 |
| INV0005    | 2007-07-28 | ORD/07-08/0004 | 2007-06-25 | 2007-07-30 | BK001   | Introduction to Electrodynamics | English  | CA001   |           8 |       25.00 |     200.00 |
| INV0006    | 2007-09-24 | ORD/07-08/0007 | 2007-09-20 | 2007-09-30 | BK003   | Guide to Networking             | Hindi    | CA003   |          20 |       45.00 |     900.00 |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+

Code :

  1. INSERT INTO testpurchase   
  2. SELECT *   
  3. FROM purchase   
  4. GROUP BY cate_id;  

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 -

  1. SELECT  invoice_no,ord_no,book_name   
  2. FROM 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

+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| invoice_no | invoice_dt | ord_no         | ord_date   | receive_dt | book_id | book_name                       | pub_lang | cate_id | receive_qty | purch_price | total_cost |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| INV0001    | 2008-07-15 | ORD/08-09/0001 | 2008-07-06 | 2008-07-19 | BK001   | Introduction to Electrodynamics | English  | CA001   |          15 |       75.00 |    1125.00 |
| INV0002    | 2008-08-25 | ORD/08-09/0002 | 2008-08-09 | 2008-08-28 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |           8 |       55.00 |     440.00 |
| INV0003    | 2008-09-20 | ORD/08-09/0003 | 2008-09-15 | 2008-09-23 | BK005   | Conceptual Physics              | NULL     | CA001   |          20 |       20.00 |     400.00 |
| INV0004    | 2007-08-30 | ORD/07-08/0005 | 2007-08-22 | 2007-08-30 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |          15 |       35.00 |     525.00 |
| INV0005    | 2007-07-28 | ORD/07-08/0004 | 2007-06-25 | 2007-07-30 | BK001   | Introduction to Electrodynamics | English  | CA001   |           8 |       25.00 |     200.00 |
| INV0006    | 2007-09-24 | ORD/07-08/0007 | 2007-09-20 | 2007-09-30 | BK003   | Guide to Networking             | Hindi    | CA003   |          20 |       45.00 |     900.00 |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+

Code :

  1. INSERT INTO testpurchase   
  2. SELECT * FROM purchase   
  3. GROUP BY cate_id   
  4. ORDER BY cate_id;  

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 -

view plaincopy to clipboardprint?

  1. SELECT  cate_id,ord_no,book_name FROM 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)