StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here is how I would do it. I assume that Stock is the main table, with an ID and an amount, and that Sold maps to Stock via an ID value, and has zero to many records for each Stock item.</p> <pre><code>SELECT Q1.id, Q1.Total1, Q2.Total2 , Q1.Total1 - COALESCE(Q2.Total2,0) as Outstanding FROM ( SELECT id, SUM(amount) as Total1 FROM Stock GROUP BY id ) as Q1 LEFT OUTER JOIN ( SELECT id, SUM(Amount) as Total2 FROM Sold GROUP BY id ) as Q2 ON Q2.id = Q1.id </code></pre> <p>Note that simply formatting your SQL into a clean way forces you to break it into logical parts and will often reveal exactly what is wrong with the query. The example above also handles correctly the cases where there is not match in the Sold table.</p> <p>Cheers, Daniel</p> <p>(Code Assumptions)</p> <pre><code>DROP TABLE Stock CREATE TABLE Stock ( id integer , amount decimal(10,2) ) INSERT INTO Stock (id, amount ) VALUES ( 1, 10.1); INSERT INTO Stock (id, amount ) VALUES ( 2, 20.2); INSERT INTO Stock (id, amount ) VALUES ( 3, 30.3); SELECT * FROM STOCK DROP TABLE Sold CREATE TABLE Sold ( id integer , amount decimal(10,2) ) INSERT INTO Sold (id, amount ) VALUES ( 1, 1.1); INSERT INTO Sold (id, amount ) VALUES ( 1, 2.2); INSERT INTO Sold (id, amount ) VALUES ( 1, 3.3); INSERT INTO Sold (id, amount ) VALUES ( 2, 2.22); SELECT * FROM Sold SELECT Q1.id, Q1.Total1, Q2.Total2 , Q1.Total1 - COALESCE(Q2.Total2,0) as Outstanding FROM ( SELECT id, SUM(amount) as Total1 FROM Stock GROUP BY id ) as Q1 LEFT OUTER JOIN ( SELECT id, SUM(Amount) as Total2 FROM Sold GROUP BY id ) as Q2 ON Q2.id = Q1.id </code></pre> <p>Results:</p> <pre><code>id Total1 Total2 Outstanding 1 10.10 6.60 3.50 2 20.20 2.22 17.98 3 30.30 30.30 </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