I have 4 MySQL tables:

**designer**: `id`, `name`

**game**: `id`, `name`, `designer_id`

**started**: `id`, `game_id`

**finished**: `id`, `game_id`

My app adds a row to `started` when a game starts. It adds a row to `finished` when the game is finished. I want to get the total count of started but not finished games, grouped by designer name. This is what I have so far:

```
SELECT designer.name, COUNT(started.game_id) AS total FROM started 
  LEFT JOIN game ON started.game_id = game.id 
  LEFT JOIN designer ON game.designer_id = designer.id 
  GROUP BY designer.name  

```

This gets me the total count of started games, grouped by their designer. But it doesn't take into account the fact that I only want started *and not finished* games. 

I tried subtracting the totals, like this:

```
SELECT designer.name, (COUNT(started.game_id) - COUNT(finished.game_id)) AS total 
  FROM started 
  LEFT JOIN game ON started.game_id = game.id 
  LEFT JOIN designer ON game.designer_id = designer.id 
  LEFT JOIN finished ON finished.game_id = started.game_id
  GROUP BY designer.name 

```

But this returns totals of `0`, although there are more started than finished games.

Example data:

**designer**

```
id | name
--------------
1  | Jim Smith

```

**game**

```
id | name | designer_id
-----------------------
1  | Test | 1

```

**started**

```
id | game_id
------------
1  | 1
2  | 1
3  | 1
4  | 1

```

**finished**

```
id | game_id
------------
1  | 1
2  | 1

```