Thanks a lot. On Tue, 26 Apr 2022, 9:09 pm Josh Barnes, <josh.bar...@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.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