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.librar...@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@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
