Hi Ma. Victoria We have a slightly different query that lists author, title, branch, location, collection (ccode), call number, copy, barcode, issue/checkout count, renewal count and local use count - you should be able to adapt it to your needs fairly easily (add limits for date, location and branch). We started from the biblio table rather than from statistics, and added an inner join for items and a left join for statistics (you should be able to use an inner join or a left join (I can never remember which does what) for biblioitems, though on second thought I'm not sure why you might be calling up that table in the first place)

SELECT b.author AS AUTHOR, b.title AS TITLE, i.homebranch, i.location, i.ccode AS COLLECTION, i.itemcallnumber AS "CALL NUMBER", i.copynumber AS COPY, i.barcode AS BARCODE, COUNT( IF( s.type = 'issue', s.itemnumber, NULL)) AS "ISSUE COUNT", COUNT( IF( s.type = 'renew', s.itemnumber, NULL)) AS "RENEWAL COUNT", COUNT( IF( s.type = 'localuse', s.itemnumber, NULL)) AS "LOCAL USE COUNT" FROM biblio b INNER JOIN items i USING (biblionumber) LEFT JOIN statistics s USING (itemnumber) WHERE s.type IN ('issue', 'renew', 'localuse') GROUP BY itemnumber ORDER BY i.homebranch, i.location, i.itemcallnumber, i.copynumber

Good luck! With kind regards from the Dalton McCaughey Library Team Carlos Lopez Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052 Ph: 03 9340 8888 ext.1 | library@dml.vic.edu.au | library.dmlibrary.org.au -----Original Message----- From: Koha <koha-bounces@lists.katipo.co.nz> On Behalf Of Ma. Victoria H. Silva-Manuel Sent: Wednesday, March 29, 2023 2:39 PM To: koha <koha@lists.katipo.co.nz> Subject: [Koha] In House Use CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe. I am using the SQL report below to generate in house use:

SELECT count(statistics.type), items.itemcallnumber 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='localuse' AND date(statistics.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND statistics.branch=<<Pick your branch|branches>> AND items.location = <<Location|loc>> GROUP BY items.itemcallnumber ORDER BY items.itemcallnumber asc

The original SQL only shows the count, I added the call number and filtered it by library and location successfully but I cannot add the title. I am always getting an error. What should I do? -- Ma. Victoria H. Silva-Manuel Registered Librarian, 3892 _______________________________________________ Koha mailing list https://urldefense.com/v3/__http://koha-community.org__;!!DVrgiXjqvl2yLjg!aw... Koha@lists.katipo.co.nz Unsubscribe: https://urldefense.com/v3/__https://lists.katipo.co.nz/mailman/listinfo/koha...