[Koha] Top 10 Circulating Books

[Cab Vinton]

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 at 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 at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha