StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POmysql join/union 0 for non-existent
text
Body
copied!<p>I'm trying to pull report data for option volumes in order to create a summary. My query is currently this:</p> <pre><code>select concat(contract, ',', group_concat(traded), ',', sum(traded)) from ( select contract, put_call, sum(oov+gv+pv) as traded from report_data where contract <> "AO" and date = '2013-01-30' group by contract, put_call order by contract asc, put_call asc ) temp group by contract </code></pre> <p>It returns:</p> <pre><code>7A,9600,0,9600 BV,100,400,500 WA,0,900,900 WM,500,500 WZ,0,0 </code></pre> <p>The reason that the last two rows are missing a number is because there are only puts for the WM, WZ contracts in the database on this day. This is as opposed to the 7A or WA, where there are data for both puts and calls but no volume (hence the zero's).</p> <p>I've been trying to join or union the table to itself in order to generate zero's where there are no puts (or calls) for a contract, but I can't seem to figure it out.</p> <p>Any suggestions?</p> <p>====================================</p> <p>Simpler:</p> <p>Data:</p> <pre><code>Contract Put_call Traded OtherData A P 10 blah A C 5 blah B P 10 blah B C 0 blah C P 10 blah </code></pre> <p>I'd like a query to result in this:</p> <pre><code>A, 5, 10, 15 B, 0, 10, 10 C, 0, 10, 10 </code></pre> <p>My query currently returns this:</p> <pre><code>A, 5, 10, 15 B, 0, 10, 10 C, 10, 10 </code></pre> <p>It's missing the zero for the C contract calls because there is no data at all, as opposed to data with zero traded.</p> <p>Please let me know if this makes sense.</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