StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can use the <a href="http://msdn.microsoft.com/en-us/library/ms180199.aspx" rel="nofollow noreferrer">HAVING</a> clause to check whether the sum of the totals exceed the credit limit, like so. I've also replaced the joins in the WHERE clause with INNER JOINS, for readability sake.</p> <pre><code> SELECT arc.company, arc.credit_limit, sum(ard.unit_price * ard.invoice_qty) as open_invoice_total, sum(od.total_qty_ord * od.unit_price) as open_orders_total FROM iqms.arprepost_detail ard INNER JOIN iqms.arprepost arp ON ard.arprepost_id = arp.id INNER JOIN iqms.arcusto arc ON arp.arcusto_id = arc.id INNER JOIN iqms.ord_detail od ON o.id = od.orders_id INNER JOIN iqms.orders o ON arc.id = o.arcusto_id GROUP BY arc.company, arc.credit_limit HAVING sum(ard.unit_price * ard.invoice_qty) + sum(od.total_qty_ord * od.unit_price) > arc.credit_limit; </code></pre> <p><strong>Edit</strong></p> <p>Just to clarify Dems comment, <code>GROUP BY</code> doesn't allow the specification of <a href="https://stackoverflow.com/questions/268429/group-by-alias-oracle">aliases</a>, hence the above query 'repeats' the sum in the <code>SELECT</code> and in the <code>HAVING</code>. This can be eliminated with nesting, although note that the <code>HAVING</code> is switched with a <code>WHERE</code>. So a DRYer version of the query is:</p> <pre><code>SELECT company, credit_limit, open_invoice_total, open_orders_total FROM ( SELECT arc.company, arc.credit_limit, sum(ard.unit_price * ard.invoice_qty) as open_invoice_total, sum(od.total_qty_ord * od.unit_price) as open_orders_total FROM iqms.arprepost_detail ard INNER JOIN iqms.arprepost arp ON ard.arprepost_id = arp.id INNER JOIN iqms.arcusto arc ON arp.arcusto_id = arc.id INNER JOIN iqms.ord_detail od ON o.id = od.orders_id INNER JOIN iqms.orders o ON arc.id = o.arcusto_id GROUP BY arc.company, arc.credit_limit ) AS nested WHERE (open_invoice_total + open_orders_total) > arc.credit_limit; </code></pre> <p>And, if you prefer, a CTE may make this more readable:</p> <pre><code>;WITH nested AS ( SELECT arc.company, arc.credit_limit, sum(ard.unit_price * ard.invoice_qty) as open_invoice_total, sum(od.total_qty_ord * od.unit_price) as open_orders_total FROM iqms.arprepost_detail ard INNER JOIN iqms.arprepost arp ON ard.arprepost_id = arp.id INNER JOIN iqms.arcusto arc ON arp.arcusto_id = arc.id INNER JOIN iqms.ord_detail od ON o.id = od.orders_id INNER JOIN iqms.orders o ON arc.id = o.arcusto_id GROUP BY arc.company, arc.credit_limit ) SELECT company, credit_limit, open_invoice_total, open_orders_total FROM nested WHERE (open_invoice_total + open_orders_total) > arc.credit_limit; </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