[Koha] Circulation by Call Number

[Ma. Victoria H. Silva-Manuel]

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

-- 
Ma. Victoria H. Silva-Manuel
Registered Librarian, 3892