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.
3 respuestas
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]>
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;
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.
Nuevas preguntas
mysql
MySQL es un sistema de gestión de bases de datos relacionales (RDBMS) gratuito y de código abierto que utiliza lenguaje de consulta estructurado (SQL). NO UTILICE esta etiqueta para otras bases de datos como SQL Server, SQLite, etc. Estas son bases de datos diferentes que utilizan sus propios dialectos de SQL para administrar los datos.