CODEWITHSUNDEEP
mysqlsql
flukyspore
flukyspore

Reputation: 696

MYSQL query - how to construct total for only ids that have a particular value in another table?

I have the two following tables. In ONE query, how can I total the column TOTAL_TIME for only those VEHICLE_IDs that have a FEATURE_ID of 2 found in TABLE 2?

Thus in my case the result of the query should be 2:30 as it would add 2:00 and 0:30 because those vehicles have a FEATURE_ID of 2.

TABLE 1
VEHICLE_ID | TOTAL_TIME
----------------+-----------------
       22         |         2:00
       25         |         2:30
       22         |         1:25
       28         |         0:30
       ........

TABLE 2
VEHICLE_ID | FEATURE_ID
----------------+-----------------
       22         |         2
       22         |         3
       23         |         1
       23         |         3
       25         |         1
       28         |         2
       ........

Upvotes: 0

Views: 44

Answers (2)

Bryan Allo
Bryan Allo

Reputation: 80

SELECT SUM(TOTAL_TIME) FROM TABLE_1 WHERE VEHICLE_ID IN (SELECT VEHICLE_ID FROM TABLE_2 WHERE FEATURE_ID = 2)

Are you looking for overall total time for FEATURE_ID = 2? OR are you looking for total overall time for FEATURE_ID = 2 grouped by VEHICLE_ID?? That would be something like:

SELECT T1.VEHICLE_ID, SUM(T1.TOTAL_TIME) 
FROM TABLE_1 T1
LEFT JOIN TABLE_2 T2 ON T2.VEHICLE_ID = T1.VEHICLE_ID AND T2.FEATURE_ID = 2
GROUP BY T1.VEHICLE_ID
ORDER BY T1.VEHICLE_ID

Upvotes: 2

Menztrual
Menztrual

Reputation: 41597

Could try something like:

SELECT SUM(t1.total_time) FROM table1 as t1 
INNER JOIN table2 as t2 ON t1.vehicle_id = t2.vehicle_id AND t2.feature_id = 2

Upvotes: 2

Related Questions