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
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 < mavicsilva@gmail.com> 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 Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- *Karam Qubsi*
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 <karamqubsi@gmail.com> 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 < mavicsilva@gmail.com> 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 Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- *Karam Qubsi*
-- Ma. Victoria H. Silva-Manuel Registered Librarian, 3892
participants (2)
-
Karam Qubsi -
Ma. Victoria H. Silva-Manuel