Hi. Thank you for helping me out. I used the SQL you provided and edit a little bit because it shows error. Below is the final script that worked.
SELECT count(statistics.type) AS 'Checkouts', (items.itemcallnumber) AS > 'Call Number', (items.barcode) AS 'Accession Number', biblio.title AS > 'Title' > FROM borrowers > LEFT JOIN statistics ON > (statistics.borrowernumber=borrowers.borrowernumber) > LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) > LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = > items.biblioitemnumber) > LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber ) > WHERE date(statistics.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> > AND <<and (yyyy-mm-dd)|date>> > AND statistics.branch = <<Branch|branches>> > AND items.location = <<Location|loc>> > AND statistics.itemtype = <<Item > type|itemtypes>> > GROUP BY items.itemcallnumber , biblio.biblionumber > ORDER BY items.itemcallnumber ASC > Thank you again and have a nice day. On Tue, Sep 3, 2019 at 3:17 PM Karam Qubsi <[email protected]> wrote: > Hello, > Try this : > > SELECT biblio.title, count(statistics.type) AS 'Checkouts', > (items.itemcallnumber) AS > 'Call Number', (items.barcode) AS 'Accession Number' > FROM borrowers > LEFT JOIN statistics ON > (statistics.borrowernumber=borrowers.borrowernumber) > LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) > LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = > items.biblioitemnumber) > LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber ) > WHERE date(statistics.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date > AND <<and (yyyy-mm-dd)|date > AND statistics.branch = <<Branch|branches > AND items.location = <<Location|loc > AND statistics.itemtype = <<Item > type|itemtypes > GROUP BY items.itemcallnumber , biblio.biblionumber > ORDER BY items.itemcallnumber ASC > > > > On Tue, Sep 3, 2019 at 2:57 PM Ma. Victoria H. Silva-Manuel < > [email protected]> wrote: > >> How do I add title in the SQL report below to generate circulation >> statistics by call number? >> >> SELECT count(statistics.type) AS 'Checkouts', (items.itemcallnumber) AS >> > 'Call Number', (items.barcode) AS 'Accession Number' >> > FROM borrowers >> > LEFT JOIN statistics ON >> > (statistics.borrowernumber=borrowers.borrowernumber) >> > LEFT JOIN items ON (items.itemnumber = statistics.itemnumber) >> > LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = >> > items.biblioitemnumber) >> > WHERE date(statistics.datetime) BETWEEN <<Date BETWEEN >> (yyyy-mm-dd)|date>> >> > AND <<and (yyyy-mm-dd)|date>> >> > AND statistics.branch = >> <<Branch|branches>> >> > AND items.location = <<Location|loc>> >> > AND statistics.itemtype = <<Item >> > type|itemtypes>> >> > GROUP BY items.itemcallnumber >> > ORDER BY items.itemcallnumber ASC >> > >> >> I tried adding biblio.title in many places but it always ending up in >> error. >> I'm not a programmer that is why I'm struggling with this. >> >> Thank you. >> >> >> >> -- >> Ma. Victoria H. Silva-Manuel >> Registered Librarian, 3892 >> _______________________________________________ >> Koha mailing list http://koha-community.org >> [email protected] >> https://lists.katipo.co.nz/mailman/listinfo/koha >> > > > -- > *Karam Qubsi* > > -- Ma. Victoria H. Silva-Manuel Registered Librarian, 3892 _______________________________________________ Koha mailing list http://koha-community.org [email protected] https://lists.katipo.co.nz/mailman/listinfo/koha
