Hi James, The query I posted worked with my quick test. Could you post your query as it is now?
On Sat, Mar 14, 2020 at 9:52 AM muiru james <[email protected]> wrote: > Hi Rogan and All, > > I cant thank you enough for the tremendous effort you have put towards > helping me solve my problem. > > You do indeed understand well what I need. > > The UNION ALL is working well by adding a new row below the last class > group. However the SUM function is returning a 0 (zero). It seems its not > adding up the numbers. > > Please dont give up but help me find out the last remaining bit. We are > almost there. > > Most appreciated > > James > > > > > > On Thu, Mar 12, 2020 at 2:57 PM <[email protected]> wrote: > >> Hi James, >> >> If I'm understanding correctly you want an additional row giving a sum of >> all the numbers in addition to the per class one? The simplest way it >> occurs to me to do that is with a UNION ALL statement. I've taken some >> filters out for brevity but this illustrates the approach: >> >> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", >> count(statistics.type) AS 'NUMBER' >> FROM borrowers >> LEFT JOIN statistics ON >> (statistics.borrowernumber=borrowers.borrowernumber) >> LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) >> LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = >> items.biblioitemnumber) >> WHERE statistics.type IN ('issue', 'renew') >> GROUP BY SUBSTRING(itemcallnumber,1,1) >> UNION ALL >> SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER' >> FROM borrowers >> LEFT JOIN statistics ON >> (statistics.borrowernumber=borrowers.borrowernumber) >> LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) >> LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = >> items.biblioitemnumber) >> WHERE statistics.type IN ('issue', 'renew') >> >> Basically with UNION ALL you can create other queries that don't have to >> be tied to the data of the previous queries so long as the columns line >> up. >> >> >> >> On Thu, Mar 12, 2020 at 3:04 AM muiru james <[email protected]> wrote: >> >>> Hi Rogan and Team, >>> >>> I really appreciate your assistance towards my problem. The query does >>> indeed work to answer my need. Another query was also pushed to me towards >>> the same. >>> >>> I'm now looking for a way to SUM my answer to get the total number of >>> books issued. Any suggestions would be most welcome for any of the 2 >>> queries. The other SQL query is as below: - >>> >>> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", >>> count(statistics.type) AS 'NUMBER' >>> FROM borrowers >>> LEFT JOIN statistics ON >>> (statistics.borrowernumber=borrowers.borrowernumber) >>> LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) >>> LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = >>> items.biblioitemnumber) >>> WHERE statistics.type IN ('issue', 'renew') >>> AND date(statistics.datetime) BETWEEN <<Date BETWEEN >>> (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> >>> AND statistics.itemtype = 'BK' >>> GROUP BY SUBSTRING(itemcallnumber,1,1) >>> ORDER BY items.itemcallnumber ASC >>> >>> N.B My desire is to SUM the NUMBER column as TOTAL. >>> >>> Please assist team >>> >>> Warm Regards. >>> JAMES >>> >>> On Fri, Mar 6, 2020 at 7:10 PM <[email protected]> wrote: >>> >>>> Hi James, >>>> >>>> One simple way to handle it is with a case statement. The >>>> following example has a static between filter for the dates but it >>>> illustrates this approach. You can also do some cleaning to make sure there >>>> aren't spaces in front and it assumes that all of your DDC numbers in fact >>>> start with a digit. >>>> >>>> SELECT >>>> COUNT(*), >>>> CASE >>>> WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s' >>>> WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s' >>>> WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s' >>>> WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s' >>>> WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s' >>>> WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s' >>>> WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s' >>>> WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s' >>>> WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s' >>>> WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s' >>>> ELSE 'OTHER' >>>> END >>>> from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i >>>> JOIN items it ON it.itemnumber = i.itemnumber >>>> WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29' >>>> GROUP BY 2 ORDER BY 2; >>>> >>>> >>>> >>>> On Thu, Mar 5, 2020 at 11:06 AM muiru james <[email protected]> >>>> wrote: >>>> >>>>> Dear All, >>>>> >>>>> My library uses DDC scheme of classification and we are looking for a >>>>> report that will count items checked out allowing us to select day(s) >>>>> of >>>>> issue, select itemtype and group the statistics by DDC 10 broad >>>>> classes. >>>>> >>>>> *EXAMPLE* >>>>> >>>>> IN a date range say 1st-31st March 2020. >>>>> >>>>> *DDC Class | NO. of books issued* >>>>> 000 - 12 >>>>> 100 - 26 >>>>> 200 - 157 >>>>> . >>>>> . >>>>> . >>>>> 900 - 230 >>>>> >>>>> The report can generate statistics for a day, month, quarter, e.t.c. >>>>> >>>>> Any assistance however close will be greatly appreciated. >>>>> >>>>> Thank you as you prepare to help >>>>> >>>>> James >>>>> _______________________________________________ >>>>> >>>>> Koha mailing list http://koha-community.org >>>>> [email protected] >>>>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha >>>>> >>>> _______________________________________________ Koha mailing list http://koha-community.org [email protected] Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
