[Koha] How to Create Top 10 Circulation book with ISBN report

[MASTeR Library]

Thanks a lot.

On Tue, 26 Apr 2022, 9:09 pm Josh Barnes, <josh.barnes at portneuflibrary.org>
wrote:

> Hello!
>
> You're on the right track with adding the bi.isbn. The extra part you
> missed is to tell your query which table the isbn is coming from. You can
> do that by using another left join to add the biblioitems table. Try
> something like this:
>
> SELECT count(s.datetime) AS circs, b.title, b.author, i.ccode, bi.isbn
> FROM statistics s
> JOIN items i ON (i.itemnumber=s.itemnumber)
> LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber)
> LEFT JOIN biblioitems bi on (i.biblionumber=bi.biblionumber)
> WHERE DATE(s.datetime) > DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH) AND
> DATE(s.datetime)<=CURRENT_DATE() AND s.itemnumber IS NOT NULL AND s.type IN
> ('issue','renew')
> GROUP BY b.biblionumber
> ORDER BY circs DESC
> LIMIT 10
>
> Cheers!
> Josh Barnes
> Portneuf Library
>
> On Tue, Apr 26, 2022 at 3:07 AM MASTeR Library <mamcet.library10 at gmail.com>
> wrote:
>
>> Hi
>> Is it possible to Create  Top 10 Circulation book  with ISBN  number in
>> SQL
>> report,
>> it is Available for Top Circulation book  report:
>>
>> https://wiki.koha-community.org/wiki/SQL_Reports_Circulation#Top_10_Circulating_Books
>> , but ISBN number missing.
>>
>> How to Add the ISBN Number in the report. i try to "bi.isbn" but not
>> working
>>
>> SELECT count(s.datetime) AS circs, b.title, b.author,
>>        i.ccode FROM statistics sJOIN items i ON
>> (i.itemnumber=s.itemnumber) LEFT JOIN biblio b ON
>> (b.biblionumber=i.biblionumber) WHERE DATE(s.datetime) >
>> DATE_SUB(CURRENT_DATE(),INTERVAL 6 MONTH)
>>       AND DATE(s.datetime)<=CURRENT_DATE() AND
>>       s.itemnumber IS NOT NULL AND s.type IN ('issue','renew')GROUP BY
>> b.biblionumber ORDER BY circs DESC LIMIT 10
>>
>>
>>
>> --
>>  Thanking you..
>>
>> Librarian,
>> MAMCET <http://mamcet.com/>
>> Trichy-Chennai Trunk Road,
>> Siruganur, Tiruchirappalli-621 105.
>> _______________________________________________
>>
>> Koha mailing list  http://koha-community.org
>> Koha at lists.katipo.co.nz
>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>