[Koha] In House Use

[Ma. Victoria H. Silva-Manuel]

Thank you so much, this is very helpful.

Btw, I have no background in programming that is why creating SQL reports
is very challenging for me.

I am grateful that the Koha community has always been supportive of my
queries.

On Wed, Mar 29, 2023 at 11:55 PM Josh Barnes <
josh.barnes at portneuflibrary.org> wrote:

> 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
>>
>

-- 
Ma. Victoria H. Silva-Manuel
Registered Librarian, 3892