[Koha] In House Use

[Josh Barnes]

Good morning,

At a guess, you are receiving an error because none of the four tables in
your query has title information in them. You will most likely need to join
the biblio table and pull titles from there. Two of those four joined
tables are also not being used by your query (borrowers and biblioitems) so
you could take those two tables out unless you wanted to add more to your
query. It would probably run faster that way. Doing that would make the
code look something like

SELECT count(statistics.type), items.itemcallnumber, biblio.title
FROM statistics
LEFT JOIN items on (items.itemnumber=statistics.itemnumber)
LEFT JOIN biblio on (items.biblionumber=biblio.biblionumber)
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, biblio.title
ORDER BY items.itemcallnumber asc

Hope that helps!
--Josh Barnes

On Tue, Mar 28, 2023 at 9:39 PM Ma. Victoria H. Silva-Manuel <
mavicsilva at gmail.com> wrote:

> 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  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>