StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You already have a placeholder. The "placeholder for <em>unknown</em> data" in SQL is null.</p> <p>You don't need to pre-fill anything: either you have a row with some columns having an unknown value (null), or you have no row at all, so that doing an outer join will get a row that is all null. Either way, the <em>attribute data</em> (essentially, non-id fields) will be null.</p> <p>And the <code>sum()</code> aggregate will ignore nulls.</p> <p>So let's say that you <em>do</em> have a row for a game (since it's pre-scheduled), but no corresponding rows for its periods (since they have not yet been played). Then you do an outer join form game to period (outer, so that you include both games <em>with</em> and games <em>without</em>, period data):</p> <pre><code>select a.*, sum(b.goals_scored) from game a left outer join period b on (b.game_id = a.id) group by a.id; </code></pre> <p>This shows you the total goals (for both teams) by game; for games with no periods, you get back null (which means in SQL, "we don't (yet) know")</p> <p>This query shows you only the total goals for completed games <em>and games in progress (games for which at least one period has been played)</em>:</p> <pre><code>select a.*, sum(b.goals_scored) from game a join period b on (b.game_id = a.id) group by a.id; </code></pre> <p>This view filters out incomplete games (assuming you always add early periods before later ones) :</p> <pre><code>create view complete_games as select a.* from games a where exists (select * from period b where b.game_id = a.id and b.number = a.number_of_periods) </code></pre> <p>Using that view, we can then sum only completed games:</p> <pre><code>select a.*, sum(b.goals_scored) from complete_games a join period b on (b.game_id = a.id) group by a.id; </code></pre> <p>So, no need to pre-fill, no need for a trigger, most importantly, no need to add false data (claiming zero goals when in fact the period has not yet been played), no need to update with correct data. Just insert the period when you have data for it. </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