[Koha] SQL problem- showing results when they are zero.

[Raymund Delahunty]

We have a quite complex budget and funds structure which is making writing reports somewhat challenging. For example, we have struggled at modify the report below, to have it show lines when both Expenditure AND Commitments are zero. When both are zero, the line is simply omitted from the result. We have not seen any results where Expenditure is zero, but where Commitments is not (a possible scenario, of course), so we are not sure if it is the Expenditures being zero that is the underlying problem. For example running the query of the CSM College library shows only 36 lines of results rather than the expected 44. So 8 have zero in both Exp / Comms. Can someone suggest how we might add in the missing 8 lines.

SELECT budget_name AS 'Fund Name', sort1 as 'Subject Fund', FORMAT(sum(valSpent),2) AS 'Expenditure', FORMAT(sum(valOnOrder),2) AS 'Commitments'
FROM
(SELECT b.budget_name, o.sort1, sum(o.unitprice*o.quantity) AS valSpent, 0 AS valOnOrder
FROM aqorders o
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.datereceived IS NOT NULL
AND o.sort1 IN ('CSM Act & Direct','CSM Animation','CSM Architecture','CSM CCC','CSM Ceramic Design','CSM Charges','CSM Collab Perf','CSM Enterprise','CSM Fashion','CSM Film','CSM Fine Art','CSM Foundation','CSM Graphic Design','CSM Innov & Imagin','CSM Jewellery','CSM PDP','CSM Photography','CSM Pro & Indu Des','CSM Qref & General','CSM Replacements','CSM Samples','CSM Textiles','CSM Journals','CSM e-Act & Direct','CSM e-Animation','CSM e-Architecture','CSM e-CCC','CSM e-Ceramic Design','CSM e-Charges','CSM e-Collab Perf','CSM e-Enterprise','CSM e-Fashion','CSM e-Film','CSM e-Fine Art','CSM e-Foundation','CSM e-Graphic Design','CSM e-Innov & Imagin','CSM e-Jewellery','CSM e-PDP','CSM e-Photography','CSM e-Pro & Indu Des','CSM e-Qref & General','CSM e-Replacements','CSM e-Samples','CSM e-Textiles','CSM e-Journals')
GROUP BY o.sort1
UNION ALL
SELECT b.budget_name, o.sort1, 0 AS valSpent, sum(o.listprice*o.quantity) AS valOnOrder
FROM aqorders o
LEFT JOIN aqbudgets b USING (budget_id)
WHERE o.sort1 IN ('CSM Act & Direct','CSM Animation','CSM Architecture','CSM CCC','CSM Ceramic Design','CSM Charges','CSM Collab Perf','CSM Enterprise','CSM Fashion','CSM Film','CSM Fine Art','CSM Foundation','CSM Graphic Design','CSM Innov & Imagin','CSM Jewellery','CSM PDP','CSM Photography','CSM Pro & Indu Des','CSM Qref & General','CSM Replacements','CSM Samples','CSM Textiles','CSM Journals','CSM e-Act & Direct','CSM e-Animation','CSM e-Architecture','CSM e-CCC','CSM e-Ceramic Design','CSM e-Charges','CSM e-Collab Perf','CSM e-Enterprise','CSM e-Fashion','CSM e-Film','CSM e-Fine Art','CSM e-Foundation','CSM e-Graphic Design','CSM e-Innov & Imagin','CSM e-Jewellery','CSM e-PDP','CSM e-Photography','CSM e-Pro & Indu Des','CSM e-Qref & General','CSM e-Replacements','CSM e-Samples','CSM e-Textiles','CSM e-Journals')
and o.datereceived IS NULL and o.orderstatus<>'cancelled'
GROUP BY o.sort1) AS CSMorders
GROUP BY sort1
ORDER BY budget_name ASC

Many thanks.

Ray Delahunty
r.delahunty at arts.ac.uk<mailto:r.delahunty at arts.ac.uk>
University of the Arts London




.
This email and any attachments are intended solely for the addressee and may contain confidential information. If you are not the intended recipient of this email and/or its attachments you must not take any action based upon them and you must not copy or show them to anyone. Please send the email back to us and immediately and permanently delete it and its attachments. Where this email is unrelated to the business of University of the Arts London or of any of its group companies the opinions expressed in it are the opinions of the sender and do not necessarily constitute those of University of the Arts London (or the relevant group company). Where the sender's signature indicates that the email is sent on behalf of London Artscom Limited the following also applies: London Artscom Limited is a company registered in England and Wales under company number 02361261. Registered Office: University of the Arts London, 272 High Holborn, London WC1V 7EY