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

[Josh Barnes]

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
>