CODEWITHSUNDEEP
sql
Alan Mulligan
Alan Mulligan

Reputation: 1198

Beginner SQL Developer, need to exclude some result from query

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

Answers (2)

Moho
Moho

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

Rapha&#235;l Althaus
Rapha&#235;l Althaus

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

Related Questions