StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Whenever you have <code>sum(distinct . . . )</code> you have a problem. You are getting cartesian products on the left outer join, and your overall results will not be accurate if there are two lines that have the same amount.</p> <p>I think you want to replace all those left outer join with a subquery like this:</p> <pre><code>select budget_item_id, Sum(case when budget_id = 5983 then amount end) AS BAL, Sum(case when budget_id = 28839 then amount end) AS BAL1, Sum(case when budget_id = 28633 then amount end) AS BAL2, Sum(case when budget_id = 26664 then amount end) AS BAL3, Sum(case when budget_id = 14500 then amount end) AS BAL4, Sum(case when budget_id = 10199 then amount end) AS BAL5, Sum(case when budget_id = 7204 then amount end) AS BAL6 from balance_lines group by budget_item_id </code></pre> <p>and then fix the rest of the query.</p> <p>Here is an attempt to write the query. This undoubtedly has syntax errors:</p> <pre><code>SELECT bi.id, bi.location, bi.expense_group, bi.level, bi.is_active, bi.type, full_name, (bl.bal) AS BudgetAmount, (coalesce(( bl.bal * 6 ) - (bl.bal1 + bl.bal2 + bl.bal3 + bl.bal4 + bl.bal5 + bl.bal6), 0)) AS Difference, (coalesce(Round(( bl.bal1 + bl.bal2 + bl.bal3 + bl.bal4 + bl.bal5 + bl.bal6 ) / 6), 0) ) AS Average, bl.bal1 AS BAL1, bl.bal2 AS BAL2, bl.bal3 AS BAL3, bl.bal4 AS BAL4, bl.bal5 AS BAL5, bl.bal6 AS BAL6 FROM (SELECT * FROM budget_items bi WHERE bi.location IS NOT NULL ) bi left outer join (select budget_item_id, Sum(case when budget_id = 5983 then amount end) AS BAL, Sum(case when budget_id = 28839 then amount end) AS BAL1, Sum(case when budget_id = 28633 then amount end) AS BAL2, Sum(case when budget_id = 26664 then amount end) AS BAL3, Sum(case when budget_id = 14500 then amount end) AS BAL4, Sum(case when budget_id = 10199 then amount end) AS BAL5, Sum(case when budget_id = 7204 then amount end) AS BAL6 from balance_lines group by budget_item_id ) bal on bal.budget_item_id = bi.id ORDER BY bi.position </code></pre> <p>Note that I entirely removed the outer <code>group by</code>, assuming that <code>bi.id</code> is a unique key on the budget items table.</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