CODEWITHSUNDEEP
pythonsqlite
sierra_papa
sierra_papa

Reputation: 394

How to count distinct values based on condition in sqlite?

My table called matchEvent looks like this:

 eventId    playerId  matchId   eventName description
 --------------------------------------------------------
 100        50        1         goal      freeKick
 100        50        1         goal      shot
 200        50        2         foul      penalty
 300        51        3         goal      header
 300        51        3         goal      ownGoal
 400        51        4         goal      ownGoal
 400        51        4         goal      shot

I want to count all regular goals (but not own goals) scored by a specific player in a specific match. I tried:

con = sqlite3.connect(my_db)
regular_goals = con.execute("SELECT playerId, matchId, COUNT(DISTINCT eventId) FROM matchEvent WHERE eventName = 'goal' GROUP BY playerId, matchId HAVING description <> 'ownGoal'").fetchall()

The problem is that HAVING clause seems to be filtering the groups based on group's top row value so it's giving me:

[(50, 1, 1),(51, 3, 1)]

while the result I expect is:

[(50, 1, 1)]

since my table contains only one regular goal.

Upvotes: 1

Views: 258

Answers (1)

forpas
forpas

Reputation: 164089

You need 2 levels of aggregation:

SELECT playerId, matchId, SUM(counter) total_goals
FROM (
  SELECT playerId, matchId, COUNT(DISTINCT eventId) counter
  FROM matchEvent  
  GROUP BY eventId, playerId, matchId 
  HAVING SUM(eventName = 'goal') > 0 AND SUM(description = 'ownGoal') = 0
) t
GROUP BY playerId, matchId

See the demo.

Or:

SELECT playerId, matchId, COUNT(*) total_goals
FROM (
  SELECT playerId, matchId
  FROM matchEvent  
  GROUP BY eventId, playerId, matchId 
  HAVING SUM(eventName = 'goal') > 0 AND SUM(description = 'ownGoal') = 0
) t
GROUP BY playerId, matchId

See the demo.

Or:

SELECT playerId, matchId, COUNT(DISTINCT eventId) total_goals
FROM matchEvent  
WHERE eventName = 'goal' 
  AND eventId NOT IN (SELECT eventId FROM matchEvent WHERE description = 'ownGoal')
GROUP BY playerId, matchId

See the demo.

Results:

| playerId | matchId | counter |
| -------- | ------- | ------- |
| 50       | 1       | 1       |

Upvotes: 1

Related Questions