Modifique una tabla SQL para condensar filas similares mientras resume una columna

Question

Aquí hay una pregunta que es la esencia general de lo que estoy tratando de hacer:

Sumar valores de varias filas en una fila

Sin embargo, que yo sepa, estoy buscando más funcionalidades, que modifiquen permanentemente la tabla en cuestión para que se vea como el resultado de la instrucción SELECT que se sugiere en ese otro hilo.

Entonces la mesa:

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

... se modificaría permanentemente para que se vea así

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

Como se responde en el enlace, usar SELECT con SUM y GROUP BY puede mostrarme cómo debe verse la tabla, pero ¿cómo puedo aplicar esos cambios a la tabla sales ?

Editar: esta consulta se ejecutará cada vez que se agregue un nuevo lote de ventas al sistema. Su objetivo es limpiar la tabla sales después de que se hayan agregado nuevos registros.

Enfoque alternativo

Los nuevos registros en ventas se insertan desde una tabla diferente usando algo como esto:

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

Si hay una manera de ocuparse de la suma durante ese INSERTAR anterior, en lugar de agregar filas duplicadas en primer lugar, eso también sería aceptable. Tenga en cuenta que esta solución aún debería funcionar para nuevos registros que no tienen filas duplicadas existentes en ventas .

EDITAR: para la posteridad

La respuesta general parece ser que mi enfoque inicial no es posible: menos que crear una tabla temp_sales con CREAR y SELECCIONAR, luego purgar completamente ventas y luego copiar el contenido de temp_sales en la tabla sales borrada, y truncando temp_sales para uso futuro.

La solución aceptada utiliza el "enfoque alternativo" al que también había aludido.

mysql sql database sum sql-update

0

Leviathan3 14 ene. 2018 a las 14:42

Answer 1

Suponiendo que new_sales se trunca después de que las ventas se hayan actualizado y luego comience a rellenar, puede usar insert..on duplicate key..update por ejemplo

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]>

0

P.Salmon 14 ene. 2018 a las 12:46

Answer 2

Esta consulta hace lo que puede hacer una herramienta ETL, pero todo lo que necesita para ejecutar el script completo:

---------- Mesa vieja

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;

0

saravanatn 14 ene. 2018 a las 12:34

Answer 3

Puede crear una tabla a partir de una instrucción select.

Entonces podrías hacer algo como:

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

Eso crea una tabla con la estructura correcta e insertará los registros que desea tener. Por supuesto, puede adaptar la consulta o el nombre de la tabla.

0

Wouter van Nifterick 14 ene. 2018 a las 11:54

Modifique una tabla SQL para condensar filas similares mientras resume una columna

3 respuestas

Nuevas preguntas

mysql