Using multiple left joins to calculate averages and counts

Question

Hi,

I am trying to figure out how to use multiple left outer joins to calculate average scores and number of cards. I have the following schema and test data. Each deck has 0 or more scores and 0 or more cards. I need to calculate an average score and card count for each deck. I'm using mysql for convenience, I eventually want this to run on sqlite on an Android phone.

mysql> select * from deck;
+----+-------+
| id | name  |
+----+-------+
|  1 | one   | 
|  2 | two   | 
|  3 | three | 
+----+-------+

mysql> select * from score;
+---------+-------+---------------------+--------+
| scoreId | value | date                | deckId |
+---------+-------+---------------------+--------+
|       1 |  6.58 | 2009-10-05 20:54:52 |      1 | 
|       2 |     7 | 2009-10-05 20:54:58 |      1 | 
|       3 |  4.67 | 2009-10-05 20:55:04 |      1 | 
|       4 |     7 | 2009-10-05 20:57:38 |      2 | 
|       5 |     7 | 2009-10-05 20:57:41 |      2 | 
+---------+-------+---------------------+--------+

mysql> select * from card;
+--------+-------+------+--------+
| cardId | front | back | deckId |
+--------+-------+------+--------+
|      1 | fron  | back |      2 | 
|      2 | fron  | back |      1 | 
|      3 | f1    | b2   |      1 | 
+--------+-------+------+--------+

I run the following query...

mysql> select deck.name, sum(score.value)/count(score.value) "Ave", 
    ->   count(card.front) "Count" 
    -> from deck 
    -> left outer join score on deck.id=score.deckId 
    -> left outer join card on deck.id=card.deckId
    -> group by deck.id;

+-------+-----------------+-------+
| name  | Ave             | Count |
+-------+-----------------+-------+
| one   | 6.0833333333333 |     6 | 
| two   |               7 |     2 | 
| three |            NULL |     0 | 
+-------+-----------------+-------+

... and I get the right answer for the average, but the wrong answer for the number of cards. Can someone tell me what I am doing wrong before I pull my hair out?

Thanks!

John

Answer 1

It's running what you're asking--it's joining card 2 and 3 to scores 1, 2, and 3--creating a count of 6 (2 * 3). In card 1's case, it joins to scores 4 and 5, creating a count of 2 (1 * 2).

If you just want a count of cards, like you're currently doing, COUNT(Distinct Card.CardId).

Answer 2

select deck.name, coalesce(x.ave,0) as ave, count(card.*) as count -- card.* makes the intent more clear, i.e. to counting card itself, not the field.  but do not do count(*), will make the result wrong
from deck    
left join -- flatten the average result rows first
(
    select deckId,sum(value)/count(*) as ave -- count the number of rows, not count the column name value.  intent is more clear
    from score 
    group by deckId
) as x on x.deckId = deck.id
left outer join card on card.deckId = deck.id -- then join the flattened results to cards
group by deck.id, x.ave, deck.name
order by deck.id

[EDIT]

sql has built-in average function, just use this:

select deckId, avg(value) as ave
from score 
group by deckId

Answer 3

Using left joins isn't a good approach, in my opinion. Here's a standard SQL query for the result you want.

select
  name,
  (select avg(value) from score where score.deckId = deck.id) as Ave,
  (select count(*) from card where card.deckId = deck.id) as "Count"
from deck;

Answer 4

What's going wrong is that you're creating a Cartesian product between score and card.

Here's how it works: when you join deck to score, you may have multiple rows match. Then each of these multiple rows is joined to all of the matching rows in card. There's no condition preventing that from happening, and the default join behavior when no condition restricts it is to join all rows in one table to all rows in another table.

To see it in action, try this query, without the group by:

select * 
from deck 
left outer join score on deck.id=score.deckId 
left outer join card on deck.id=card.deckId;

You'll see a lot of repeated data in the columns that come from score and card. When you calculate the AVG() over data that has repeats in it, the redundant values magically disappear (as long as the values are repeated uniformly). But when you COUNT() or SUM() them, the totals are way off.

There may be remedies for inadvertent Cartesian products. In your case, you can use COUNT(DISTINCT) to compensate:

select deck.name, avg(score.value) "Ave", count(DISTINCT card.front) "Count" 
from deck 
left outer join score on deck.id=score.deckId 
left outer join card on deck.id=card.deckId
group by deck.id;

This solution doesn't solve all cases of inadvertent Cartesian products. The more general-purpose solution is to break it up into two separate queries:

select deck.name, avg(score.value) "Ave"
from deck 
left outer join score on deck.id=score.deckId 
group by deck.id;

select deck.name, count(card.front) "Count" 
from deck 
left outer join card on deck.id=card.deckId
group by deck.id;

Not every task in database programming must be done in a single query. It can even be more efficient (as well as simpler, easier to modify, and less error-prone) to use individual queries when you need multiple statistics.