Modify a SQL table to condense similar rows while summing up a column

Here is a question that is the general gist of what I am trying to do:

Sum values from multiple rows into one row

However, to my knowledge, I am seeking further functionality, which would permanently modify the table in question to look like the result of the SELECT statement that is being suggested in that other thread.

So the table:

Sales
--------------------------------------
account    product    qty    amount
--------------------------------------
01010      bottle     10     200
01010      bottle     20     100
01010      bottle     5      10
11111      can        50     200
11111      can        25     150

...would be permanently modified to look like this

Sales
--------------------------------------
account    product    qty    amount
--------------------------------------
01010      bottle     35     310
11111      can        75     350

As is answered in the link, using a SELECT with SUM and GROUP BY can show me what the table needs to look like, but how to I actually apply those changes to the sales table?

edit: This query will be run every time a new batch of sales is added into the system. It's intended to clean up the sales table after new records have been added.

Alternative approach

New records in sales are inserted in from a different table using something like this:

"INSERT INTO sales
    SELECT account, product, qty, amount
    FROM new_sales;"

If there is a way to take care of the summation during that previous INSERT, instead of adding duplicate rows in the first place, that would also be acceptable. Keep in mind, this solution would still need to work for new records that don't have existing duplicate rows in sales.

EDIT: for posterity

General response seems to be that my initial approach is not possible-- short of creating a temp_sales table with a CREATE and SELECT, then purging sales completely, and then copying the contents of temp_sales into the cleared sales table, and truncating temp_sales for future use.

The accepted solution uses the "Alternative approach" that I had also alluded to.

3 answers

  • answered 2018-01-14 11:42 Wouter van Nifterick

    You can create a table from a select statement.

    So you could do something like:

    create table sales_sum as 
      select 
         account,
         product,
         sum(qty),
         sum(amount) 
       from 
         sales
       group by 
         account, 
         product
    

    That creates a table with the right structure, and it'll insert the records that you want to have. Of course you can adapt the query or the table name.

  • answered 2018-01-14 11:42 saravanatn

    This query does what a ETL tool can do but every you need to run the entire script :

    ----------Old Table

    CREATE TABLE yourtable (
      [state] varchar(2),
      [month] varchar(7),
      [ID] int,
      [sales] int
    )
    ;
    INSERT INTO yourtable ([state], [month], [ID], [sales])
      VALUES ('FL', 'June', 0001, '12000'),
      ('FL', 'June', 0001, '6000'),
      ('FL', 'June', 0001, '3000'),
      ('FL', 'July', 0001, '6000'),
      ('FL', 'July', 0001, '4000'),
      ('TX', 'January', 0050, '1000'),
      ('MI', 'April', 0032, '5000'),
      ('MI', 'April', 0032, '8000'),
      ('CA', 'April', 0032, '2000');
    
    SELECT
      state,
      month,
      id,
      SUM(sales) Total
    FROM yourtable
    GROUP BY state,
             month,
             id;
    
    -----Creating new table from old table
    
    CREATE TABLE yourtable1 (
      [state] varchar(2),
      [month] varchar(7),
      [ID] int,
      [sales] int
    )
    ;
    
    ----Inserting aggregation logic
    
    INSERT INTO yourtable1 (state, month, id, sales)
      SELECT
        state,
        month,
        id,
        SUM(sales)
      FROM yourtable
      GROUP BY state,
               month,
               id;
    -----Fetching records
    
    SELECT
      *
    FROM yourtable1;
    

  • answered 2018-01-14 11:42 P.Salmon

    Assuming new_sales is truncated after sales has been updated and then starts to refill you could use insert..on duplicate key..update for example

    MariaDB [sandbox]> drop table if exists t,t1;
    Query OK, 0 rows affected (0.20 sec)
    
    MariaDB [sandbox]>
    MariaDB [sandbox]> create table t
        -> (account varchar(5),    product varchar(20),    qty  int default 0,  amount int default 0);
    Query OK, 0 rows affected (0.16 sec)
    
    MariaDB [sandbox]> create table t1
        -> (account varchar(5),    product varchar(20),    qty  int default 0,  amount int default 0);
    Query OK, 0 rows affected (0.24 sec)
    
    MariaDB [sandbox]>
    MariaDB [sandbox]> alter table t
        -> add unique key k1(account,product);
    Query OK, 0 rows affected (0.15 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    MariaDB [sandbox]>
    MariaDB [sandbox]> truncate table t1;
    Query OK, 0 rows affected (0.23 sec)
    
    MariaDB [sandbox]> insert into t1 values
        -> ('01010'  ,    'bottle'   ,  10   ,  200),
        -> ('01010'  ,    'bottle'   ,  20   ,  100),
        -> ('01010'  ,    'bottle'   ,  5    ,  10),
        -> ('11111'  ,    'can'      ,  50   ,  200),
        -> ('11111'  ,    'can'      ,  25   ,  150);
    Query OK, 5 rows affected (0.02 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    MariaDB [sandbox]>
    MariaDB [sandbox]> truncate table t;
    Query OK, 0 rows affected (0.28 sec)
    
    MariaDB [sandbox]> insert into t
        -> select account,product,t1qty,t1amount
        -> from
        -> (
        -> select t1.account,t1.product,sum(t1.qty) t1qty,sum(t1.amount) t1amount from t1 group by t1.account,t1.product
        -> ) s
        -> on duplicate key
        -> update qty = t.qty + t1qty, amount = t.amount + t1amount;
    Query OK, 2 rows affected (0.02 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    MariaDB [sandbox]>
    MariaDB [sandbox]> truncate table t1;
    Query OK, 0 rows affected (0.32 sec)
    
    MariaDB [sandbox]> insert into t1 values
        -> ('01010'  ,    'bottle'   ,  10   ,  200),
        -> ('01011'  ,    'bottle'   ,  20   ,  100),
        -> ('01011'  ,    'bottle'   ,  5    ,  10),
        -> ('11111'  ,    'can'      ,  50   ,  200),
        -> ('11111'  ,    'can'      ,  25   ,  150);
    Query OK, 5 rows affected (0.02 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    MariaDB [sandbox]>
    MariaDB [sandbox]> insert into t
        -> select account,product,t1qty,t1amount
        -> from
        -> (
        -> select t1.account,t1.product,sum(t1.qty) t1qty,sum(t1.amount) t1amount from t1 group by t1.account,t1.product
        -> ) s
        -> on duplicate key
        -> update qty = t.qty + t1qty, amount = t.amount + t1amount;
    Query OK, 5 rows affected (0.02 sec)
    Records: 3  Duplicates: 2  Warnings: 0
    
    MariaDB [sandbox]>
    MariaDB [sandbox]>
    MariaDB [sandbox]> select * from t;
    +---------+---------+------+--------+
    | account | product | qty  | amount |
    +---------+---------+------+--------+
    | 01010   | bottle  |   45 |    510 |
    | 11111   | can     |  150 |    700 |
    | 01011   | bottle  |   25 |    110 |
    +---------+---------+------+--------+
    3 rows in set (0.00 sec)
    
    MariaDB [sandbox]>