new to SQL.
Need help with some query. In the picture attached, it shows that one "ID" has different parts.
SET - shows the combined parts (A+B+C). The WEIGHT is the total of the parts, but not the LENGTH.
The SET LENGTH is 0, and I need to get the highest LENGTH from the other parts.
Ex:
- ID=1, length is 30 (highest length)
- ID=2, length is 40 (highest length)
Thank you!
Answer
Maybe you shouldn't store partial "SET" results in the table of A, B, C records.
Given you might have MySQL and you have the following table definition and inserts of A, B, C records:
CREATE TABLE SOURCE (
ID INT,
PART VARCHAR(10),
QTY INT,
WEIGHT INT,
LENGTH INT
);
INSERT INTO SOURCE (ID,
PART,
QTY,
WEIGHT,
LENGTH)
VALUES (1, 'A', 2, 2, 30),
(1, 'A', 1, 2, 25),
(1, 'B', 2, 2, 20),
(1, 'B', 1, 2, 15),
(1, 'C', 1, 2, 10),
(2, 'A', 1, 3, 25),
(2, 'A', 1, 3, 40),
(2, 'B', 1, 2, 5);
You can have the desired report:
SELECT ID,
MAX(LENGTH) AS LENGTH,
SUM(WEIGHT) AS WEIGHT
FROM SOURCE
GROUP BY ID;
Output:
+------+--------+--------+
| ID | WEIGHT | LENGTH |
+------+--------+--------+
| 1 | 10 | 30 |
| 2 | 8 | 40 |
+------+--------+--------+