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 | libr...@dml.vic.edu.au | library.dmlibrary.org.au
-----Original Message-----
From: Koha <koha-boun...@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!awLbOIv_hmNvH3eTVS3SOke5WYZEeKLdkF8ZRi7sVzaK0BaPSowsNR68OmmhmlrxePHK4SXrvEyHXgoeatAZH4ry$
Koha@lists.katipo.co.nz
Unsubscribe:
https://urldefense.com/v3/__https://lists.katipo.co.nz/mailman/listinfo/koha__;!!DVrgiXjqvl2yLjg!awLbOIv_hmNvH3eTVS3SOke5WYZEeKLdkF8ZRi7sVzaK0BaPSowsNR68OmmhmlrxePHK4SXrvEyHXgoeatnYQZk8$
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
