How to Create Top 10 Circulation book with ISBN report
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_Circulat... , 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.
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@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_Circulat... , 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
Thanks a lot. On Tue, 26 Apr 2022, 9:09 pm Josh Barnes, <josh.barnes@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@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_Circulat... , 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
participants (2)
-
Josh Barnes -
MASTeR Library