Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2014
    Posts
    2

    Unhappy Unanswered: MYSQL Query OR staement not working

    Problem: In the query below it does run through the Penalty1 column and provides the proper count total. The thing the query is not doing is this part of the code "OR Stats.Penalty2 <> 24 OR Stats.Penalty3 <> 24". The rest of the query works fine. Any help appreciated.


    Sample STATS table below:

    GameKey | Team | Penalty1 | Penalty2 | Penalty3
    ----------------------------------------
    1000 | Team A5 | 15 | 16 | 24

    1001 | Team A5 | 23 | 24 | 24


    Sample PENALTYTYPES tables below (just a few rows to get the idea):

    Type | Description
    ------------------
    15 | Body Checking

    16 | Fighting

    23 | Roughing

    24| None

    After Running the query below the result should be

    "Team A5" "3"

    because I want anything counted that is not 24 in the three penalty columns.

    The query below outputs

    "Team A5" "2"

    because it is only Counting the column Penalty1

    In the query below it does run through the Penalty1 column and provides the proper count total. The thing the query is not doing is this part of the code "OR Stats.Penalty2 <> 24 OR Stats.Penalty3 <> 24". The rest of the query works fine.

    Code:
    SELECT COUNT(PT0.Type),
     COUNT(PT1.Type),
     COUNT(PT2.Type), 
     COUNT((PT0.Type) + (PT1.Type) + (PT2.Type)) AS PT, 
     Stats.Team, 
     PT0.Type, 
     Stats.GameKey, 
     Games.GameKey
    FROM Stats 
     INNER JOIN PenaltyTypes AS PT0 ON PT0.Type = Stats.Penalty1 
     INNER JOIN PenaltyTypes AS PT1 ON PT1.Type = Stats.Penalty2 
     INNER JOIN PenaltyTypes AS PT2 ON PT2.Type = Stats.Penalty3 
     INNER JOIN Games ON Games.GameKey = Stats.GameKey 
    WHERE (Stats.Penalty1 <> 24 OR Stats.Penalty2 <> 24 OR Stats.Penalty3 <> 24) 
    GROUP BY Stats.Team 
    ORDER BY Stats.Team

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    You need to do something like the below example so you only sum(i.e. increment the counter) if the type column is not equal to 24:

    Code:
    select sum(case when pt0.type <> 24 then 1 else 0 end) as pt0_count,
           sum(case when pt1.type <> 24 then 1 else 0 end) as pt1_count,
           sum(case when pt2.type <> 24 then 1 else 0 end) as pt2_count,
           ( sum(case when pt0.type <> 24 then 1 else 0 end) +
             sum(case when pt1.type <> 24 then 1 else 0 end) +
             sum(case when pt2.type <> 24 then 1 else 0 end) ) as pt, 
           stats.team, pt0.type, stats.gamekey, games.gamekey
     from  stats 
     inner join penaltytypes as pt0 on pt0.type      = stats.penalty1 
     inner join penaltytypes as pt1 on pt1.type      = stats.penalty2 
     inner join penaltytypes as pt2 on pt2.type      = stats.penalty3 
     inner join games               on games.gamekey = stats.gamekey 
    where (stats.penalty1 <> 24 or stats.penalty2 <> 24 or stats.penalty3 <> 24) 
    group by stats.team 
    order by stats.team

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Note: games table was omitted.
    Code:
    SELECT COUNT( CASE pt.Type
                  WHEN s.Penalty1 THEN 0
                  END
                ) AS count_pt1
         , COUNT( CASE pt.Type
                  WHEN s.Penalty2 THEN 0
                  END
                ) AS count_pt2
         , COUNT( CASE pt.Type
                  WHEN s.Penalty3 THEN 0
                  END
                ) AS count_pt3
         , COUNT( pt.Type ) AS cnt_pt_all
         , s.Team
     FROM  stats        AS s
     INNER JOIN
           penaltytypes AS pt
      ON   pt.Type
           IN (s.Penalty1 , s.Penalty2 , s.Penalty3) 
      AND  pt.Type <> 24
     GROUP BY
           s.Team 
     ORDER BY
           s.Team
    ;

    By the way,
    Why did you(123webman123) named like "PenaltyTypes AS PT0", "PenaltyTypes AS PT1" and "PenaltyTypes AS PT2"(pt number start from 0) in your query?
    Though, you named Penalty1, Penalty2 and Penalty3(Penalty number start from 1) in STATS table.

  4. #4
    Join Date
    Mar 2014
    Posts
    2
    Thanks spacebar, it works great!!!!!

    Tonkuma, someone else was helping me with it and named the aliases the way they are, I just stuck with it. No reason.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... someone else was helping me with it and named the aliases the way they are, I just stuck with it. No reason.
    OK! 123webman123.


    Then, how about this query?
    (worked? or got error? or got incorrect result?)
    Quote Originally Posted by tonkuma View Post
    How about this?

    Note: games table was omitted.
    Code:
    SELECT COUNT( CASE pt.Type
                  WHEN s.Penalty1 THEN 0
                  END
                ) AS count_pt1
         , COUNT( CASE pt.Type
                  WHEN s.Penalty2 THEN 0
                  END
                ) AS count_pt2
         , COUNT( CASE pt.Type
                  WHEN s.Penalty3 THEN 0
                  END
                ) AS count_pt3
         , COUNT( pt.Type ) AS cnt_pt_all
         , s.Team
     FROM  stats        AS s
     INNER JOIN
           penaltytypes AS pt
      ON   pt.Type
           IN (s.Penalty1 , s.Penalty2 , s.Penalty3) 
      AND  pt.Type <> 24
     GROUP BY
           s.Team 
     ORDER BY
           s.Team
    ;

    ...
    ...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •