Try this -- SELECT count(s.datetime) AS circs, b.title, b.author, i.ccode FROM statistics s JOIN items i ON (i.itemnumber=s.itemnumber) LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber) WHERE s.datetime BETWEEN <<between (date)|date>> AND <<and|date>> AND s.itemnumber IS NOT NULL AND s.type IN ('issue','renew') AND i.homebranch=<<owning branch|branches>> GROUP BY b.biblionumber ORDER BY circs DESC LIMIT 10 All best, Cab Vinton, Director Plaistow Public Library Plaistow, NH On Fri, Aug 30, 2019 at 4:26 AM Ma. Victoria H. Silva-Manuel <mavicsilva@gmail.com> wrote:
HI.
I got the SQL report below to generate the top 10 circulating books from koha-community.
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
How can we add date range and branch filter?
Thank you.
-- Ma. Victoria H. Silva-Manuel Registered Librarian, 3892 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha