Rieccomi…
CREATE TABLE ps_stock (
id_stock INTEGER PRIMARY KEY,
physical_quantity DOUBLE NOT NULL,
reference TEXT NOT NULL
);
CREATE TABLE ps_stock_mvt (
id_stock_mvt INTEGER PRIMARY KEY,
date_add DATE NOT NULL,
id_stockf integer,
sign DOUBLE NOT NULL
);
INSERT INTO ps_stock VALUES (0001, 20, 'ARTICOLO 1');
INSERT INTO ps_stock VALUES (0002, 5, 'ARTICOLO 2');
INSERT INTO ps_stock VALUES (0003, 12, 'ARTICOLO 3');
INSERT INTO ps_stock_mvt VALUES (0001, '2024-02-01', 0001, 5);
INSERT INTO ps_stock_mvt VALUES (0002, '2024-02-01', 0001, 7);
INSERT INTO ps_stock_mvt VALUES (0003, '2024-02-02', 0001, -1);
INSERT INTO ps_stock_mvt VALUES (0004, '2024-02-01', 0001, -5);
INSERT INTO ps_stock_mvt VALUES (0005, '2024-02-01', 0002, 5);
INSERT INTO ps_stock_mvt VALUES (0006, '2024-02-01', 0003, 7);
INSERT INTO ps_stock_mvt VALUES (0007, '2024-02-03', 0002, -1);
INSERT INTO ps_stock_mvt VALUES (0008, '2024-02-01', 0003, -5);
giusto per usare i campi interessati ed inserire qualche dato di prova ottengo:
+----------+-------------------+------------+
| id_stock | physical_quantity | reference |
+----------+-------------------+------------+
| 1 | 20 | ARTICOLO 1 |
| 2 | 5 | ARTICOLO 2 |
| 3 | 12 | ARTICOLO 3 |
+----------+-------------------+------------+
+--------------+------------+-----------+------+
| id_stock_mvt | date_add | id_stockf | sign |
+--------------+------------+-----------+------+
| 1 | 2024-02-01 | 1 | 5 |
| 2 | 2024-02-01 | 1 | 7 |
| 3 | 2024-02-02 | 1 | -1 |
| 4 | 2024-02-01 | 1 | -5 |
| 5 | 2024-02-01 | 2 | 5 |
| 6 | 2024-02-01 | 3 | 7 |
| 7 | 2024-02-03 | 2 | -1 |
| 8 | 2024-02-01 | 3 | -5 |
+--------------+------------+-----------+------+
la query che hai fatto non funziona: controlla i messaggi di errore.
Poichè ho tanto altro da fare, potresti dirmi, in soldoni, cosa vorresti ottenere?
SELECT reference, sum(sign), date_add
from ps_stock_mvt
inner join ps_stock on id_stockf=id_stock
group by id_stockf, date_add
risultato:
+------------+-----------+------------+
| reference | sum(sign) | date_add |
+------------+-----------+------------+
| ARTICOLO 1 | 7 | 2024-02-01 |
| ARTICOLO 1 | -1 | 2024-02-02 |
| ARTICOLO 2 | 5 | 2024-02-01 |
| ARTICOLO 3 | 2 | 2024-02-01 |
| ARTICOLO 2 | -1 | 2024-02-03 |
+------------+-----------+------------+
Le due tabelle sono di prestashop?
fammi sapere.