CODEWITHSUNDEEP
mysqldatabase
Juhani
Juhani

Reputation: 83

mysql select sum of rows by comparing two relations

I have data from tests with two lists of parts, called in and out. I need to select SUM of test values for each part after the last test where the part went in but didn't come out.

IN LIST                 OUT LIST                 TEST
+--------+-----------+  +--------+------------+  +------+-------+
| testid | in_partid |  | testid | out_partid |  | test | value |
+--------+-----------+  +--------+------------+  +------+-------+
|      1 |        10 |  |      1 |         10 |  |    1 |     1 |
|      1 |        20 |  |      1 |         20 |  |    2 |    10 |
|      2 |        10 |  |      2 |         10 |  |    3 |   100 |
|      2 |        20 |  |        |            |  |      |       |
|      3 |        10 |  |      3 |         10 |  |      |       |
|      3 |        20 |  |      3 |         20 |  |      |       |
+--------+-----------+  +--------+------------+  +------+-------+

SUM is pretty straightforward, but can I limit it to those rows where testid is greater than testid for the last inspection where part went in but not out?

In this example, part 10 should SUM all three test values, because it's included in all lists, but part 20 should only return value for test 3, as in test 2 it was not included in both in and out lists.

partid  sum(value)
    10        111
    20        100

Can I do with with mysql, or do I need to include php in the mix?

Upvotes: 0

Views: 350

Answers (1)

liquorvicar
liquorvicar

Reputation: 6106

I think your sample output is incorrect from your logic. I think partid 20 should return 101 as it is present in both lists for both tests 1 and 3. Assuming I'm right in that, this query should return the desired results

SELECT in_partid,SUM(value)
FROM (
    SELECT DISTINCT in_partid,inl.testid
    FROM in_list inl
    INNER JOIN out_list outl ON in_partid=out_partid AND inl.testid=outl.testid
    ) as tests_passed
  INNER JOIN tests ON tests_passed.testid=test
GROUP BY in_partid

EDIT: based on OP's comment my assumption above was wrong and was actually a requirement. Accordingly here is a query that I think fulfils the requirements:

SELECT tests_passed.in_partid,SUM(value)
FROM (
    SELECT DISTINCT inl.in_partid,IFNULL(last_failed_test,0) as last_failed_test
    FROM in_list inl LEFT JOIN (
        SELECT in_partid,MAX(inl.testid) as last_failed_test
        FROM in_list inl
        LEFT JOIN out_list outl ON in_partid=out_partid AND inl.testid=outl.testid
        WHERE outl.testid IS NULL
        GROUP BY in_partid
    ) AS last_passed
    ON inl.in_partid=last_passed.in_partid 
) as tests_passed
INNER JOIN tests ON tests_passed.last_failed_test<test
GROUP BY tests_passed.in_partid

This returns the sample results given above for the sample data supplied.

Upvotes: 1

Related Questions