StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POVery complicated query, not sure if it possible. Involves not adding to a running sum
text
Body
copied!<p>I have a very complicated mySQL query I want to run, and I am having trouble thinking of how to write it.</p> <p>Basically I have three tables:</p> <pre><code>a(aID, name); b(bID, val); ab(aID, bID, include) </code></pre> <p>What I want to do is create a query that creates a table like this:</p> <pre><code>c(aID, percentage); </code></pre> <p>I want percentage to be (total/possible) in this way. Total is calculated like this. Everytime an a row and b row have a relation AND include is 1 I want total to add the value of b.val. Possible will be the sum of b.val except where ab.include = 0 </p> <p>For example:</p> <pre><code>a: +-----+ | aID | +-----| | 1 | | 2 | | 3 | +-----+ b: +-----+-----+ | bID | val | +-----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | +-----+-----+ ab: +-----+-----+---------+ | aID | bID | include | +-----+-----+---------+ | 1 | 1 | 1 | | 1 | 3 | 0 | | 2 | 1 | 1 | | 3 | 1 | 1 | | 3 | 2 | 1 | | 3 | 3 | 1 | +-----+-----+---------+ c: +-----+------------+ | aID | percentage | +-----+------------+ | 1 | 50 | | 2 | 33.33 | | 3 | 100 | +-----+------------+ </code></pre> <p>Currently I have this:</p> <pre><code>SELECT a.aID, (SUM(b.val)/(SELECT SUM(val) FROM b))*100 AS percentage FROM a, ab, b WHERE a.aID = ab.aID AND ab.bID = b.bID GROUP BY a.aID </code></pre> <p>This gives the percentage aID is paired with bID with respect to weight, but I wanted to add this other calculation. </p> <p>Is this possible? </p> <p>Tried this but it didn't work:</p> <p>I was about to try this:</p> <pre><code>SELECT a.aID, (SUM(b.val)/(SELECT SUM(val) FROM b,ab WHERE ab.include = 1))*100 AS percentage FROM a, ab, b WHERE a.aID = ab.aID AND ab.bID = b.bID AND ab.include = 1 GROUP BY a.aID </code></pre>

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