Reputation: 1198
I have an sql query to display all the sum of stage_times from the stage_time table. But I only require sum of stage_times for cars that have values entered for all 3 stages so car_no 3 is excluded from the list in this case;
Here is an sqlfiddle with my example code http://sqlfiddle.com/#!2/1675d/78
SELECT d.*, SUM(Stage_Time) AS total
FROM Driver d, Stage_Times st
WHERE st.Driver_Id = d.Driver_Id AND st.Event_Id = 1
AND st.Stage_Id <= 3
GROUP BY d.Driver_Id
ORDER BY total;
Thanks in advance for any help
Upvotes: 3
Views: 262
Reputation: 16573
select
d.*
, tt.TotalTime
from
(
select
d.Driver_Id
, SUM( st.Stage_Time ) TotalTime
from
Driver d
inner join Stage_Times st
on st.Driver_Id = d.Driver_Id
where
st.Event_Id = 1
group by
d.Driver_Id
having
count(1) = 3
) tt
inner join Driver d
on tt.Driver_Id = d.Driver_id
Upvotes: 0
Reputation: 60493
You may just add an Having clause
having count(distinct st.Stage_id) = 3
see updated sqlfiddle
(by the way, I changed your query to use join, it's a better way to do).
Upvotes: 5