CODEWITHSUNDEEP
sql
Bill
Bill

Reputation: 37

SQL Select rows with value that appears x times

SELECT Opponent, JerseyNumber, A
FROM (SELECT * FROM Games_t INNER JOIN GameStats_t ON Games_t.GameID=GameStats_t.GameID)
WHERE A >=1 AND COUNT(Opponent) >3;

I'm trying to return games where there were at least three players who recorded one assist or more. If I don't have AND COUNT(Opponent) >3, the query returns almost what I want, but there are a few games where only three players recorded an assist.

Upvotes: 0

Views: 826

Answers (3)

ngrashia
ngrashia

Reputation: 9894

Use the following Query.

SELECT G_TEMP.GAME_ID, GT.OPPONENT, GT.JERSEYNUMBER, G.A FROM
(
      SELECT GAME_ID, COUNT(OPPONENT) OPP_COUNT FROM GAMESTATS_T 
      HAVING COUNT(OPPONENT) > 3
      GROUP BY GAME_ID
) G_TEMP
LEFT OUTER JOIN
     GAMES_T  G
     ON
     G.GAME_ID = G_TEMP.GAME_ID
     AND G.A > 1
     INNER JOIN
            GAMESTATS_T GT
            ON 
            G.GAME_ID = GT.GAME_ID

Working SQL Fiddle HERE

Note 1: When there are more than one table, it is always better to specify fields using tablename_alias.field_name syntax. This is a good practice, however it is optional.

For example, if Table TABLEA has fields FIELDA1, FIELDA2, FIELDA3 and if Table TABLEB has fields FIELDB1, FIELDB2

Then you can use query as:

SELECT A.FIELDA1, A.FIELDA3, B.FIELDB2
FROM TABLEA A JOIN TABLEB B ON A.FIELDA2 = B.FIELDB2

Upvotes: 1

dev
dev

Reputation: 961

Try this : 

 SELECT   Opponent, 
          JerseyNumber, 
          A,  
          COUNT(Opponent) FROM
          (
          SELECT    * 
          FROM  Games_t INNER JOIN GameStats_t 
          ON    Games_t.GameID=GameStats_t.GameID
          )
         WHERE A >=1  
         GROUP BY Opponent, JerseyNumber, A 
         HAVING COUNT(Opponent) >3

Upvotes: 1

Justinas
Justinas

Reputation: 43451

Use HAVING part in query to use some parameters after query completion:

SELECT Opponent, JerseyNumber, A
FROM (SELECT * FROM Games_t INNER JOIN GameStats_t ON Games_t.GameID = GameStats_t.GameID)
WHERE A >=1 HAVING COUNT(Opponent) > 3;

Upvotes: 0

Related Questions