Databases
StackOverflow2013
Postsdbo
POUsing multiple left joins to calculate averages and counts
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing multiple left joins to calculate averages and counts
    text
    copied!<p>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.</p> <pre> mysql> select * from deck; +----+-------+ | id | name | +----+-------+ | 1 | one | | 2 | two | | 3 | three | +----+-------+ </pre> <pre> 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 | +---------+-------+---------------------+--------+ </pre> <pre> mysql> select * from card; +--------+-------+------+--------+ | cardId | front | back | deckId | +--------+-------+------+--------+ | 1 | fron | back | 2 | | 2 | fron | back | 1 | | 3 | f1 | b2 | 1 | +--------+-------+------+--------+ </pre> <p>I run the following query...</p> <pre> 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 | +-------+-----------------+-------+ </pre> <p>... 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?</p> <p>Thanks!</p> <p>John</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload