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 <muirunyeri@gmail.com> 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 Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha