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