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.bar...@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 < > mavicsi...@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@lists.katipo.co.nz >> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha >> > -- Ma. Victoria H. Silva-Manuel Registered Librarian, 3892 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
