Databases
StackOverflow2013
Postsdbo
PODatabase query to handle when specific rows don't exist
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase query to handle when specific rows don't exist
    primarykey
    data
    text
    <p>Sorry for the bad title, it is hard to explain in one sentence what I am having problems with. So I have a stats database for basketball. What I am trying to do is group the stats for one team in one row then group all the stats this team played against in one other row. What I have now works if there are opponent stats in the database, but it is possible to not have opponent stats and just have the team stats. Hard to explain why, so please just go with that.</p> <p>So lets say I have 3 rows like this:</p> <pre><code>COMP_ID | TEAM_ID | OFF_REB | DEF_REB 1 | 100 | 5 | 4 2 | 100 | 1 | 1 3 | 100 | 3 | 7 </code></pre> <p>Basically, there are 3 games, and I only have team 100's stats. I would like to get all of the OFF and DEF rebounds for team 100 in a row and then all of the opponent OFF and DEF rebounds in another row. If there are no opponent stats, just 0's will be fine.</p> <p>FYI - What I do now is do a group by to package the team 100's stats together and also to package the opponent stats together. For the TEAM_ID of the opponent stats, I group them into -999 value so I know they are opponent values.</p> <p>Here is the query I have now:</p> <pre><code>SELECT CASE WHEN TEAM_ID &lt;&gt; 100 THEN -999 ELSE 100 END AS TEAM_ID, SUM(OFF_REB+DEF_REB) REBS FROM V_STATS_COMP WHERE COMP_ID IN (SELECT COMP_ID FROM COMPETITIONS WHERE HOME_ID = 100 OR VIS_ID = 100) GROUP BY CASE WHEN TEAM_ID &lt;&gt; 100 THEN -999 ELSE 100 END ORDER BY TEAM_ID </code></pre> <p>With this query, I get only 1 row back and thats for team 100. How do I adjust it to get the -999 row even if there are no stats available?</p> <p>Edit to show desired results: </p> <pre><code>TEAM_ID | REBS 100 | 21 -999 | 0 </code></pre> <p>Of course if there were opponent stats, id like to see their REBS value.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTravyguy9
    1. USTravyguy9
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COwhat is your subquery returning if you run it on its own?
      singulars
      1. PODatabase query to handle when specific rows don't exist
      1. USHugo V
    2. COIn this example, it would return 1,2,3 (all 3 competitions). It could return more competitions if they are in the future (havent been played yet). so it could be 1,2,3,4,5,6....100.
      singulars
      1. PODatabase query to handle when specific rows don't exist
      1. USTravyguy9
    3. COcan you please post an example of the results you'd like to see?
      singulars
      1. PODatabase query to handle when specific rows don't exist
      1. USswasheck
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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