[Koha] How to get statistic for borrowed item by borrower with department

[Katrin Fischer]

Hi ikbar,

a list of patrons with the number of current checkouts (not returned
yet) ordered by department could look something like that:

SELECT
   sort1 as department,
   cardnumber,
   surname,
   firstname,
   (select count(*) from issues i where i.borrowernumber =
b.borrowernumber) as Checkouts
FROM
   borrowers b
GROUP BY b.borrowernumber
ORDER BY sort1, surname, firstname

That's only one option. If you also want to count checkouts already
returned, you could use the statistics table:

SELECT
   sort1 as department,
   cardnumber,
   surname,
   firstname,
   COUNT(*) as checkouts
FROM
   borrowers b
   JOIN statistics s ON (b.borrowernumber = s.borrowernumber)
   WHERE DATE(datetime) between <<From|date>> and <<Until|date>>
GROUP BY b.borrowernumber
ORDER BY sort1, surname, firstname

Note: There is almost always more than one way to do it :)

Keep in mind if you:

- Want current checkouts or all checkouts in a date range
- Want to include only information about current patrons or deleted
patrons as well

You can find more examples for reports in the Reports Library on the wiki:

https://wiki.koha-community.org/wiki/SQL_Reports_Library

To get a list of checkouts with information about the item, this one
could be a good starting point:

https://wiki.koha-community.org/wiki/SQL_Reports_Circulation#All_Checked_Out_Books

Hope that helps,

Katrin


On 08.04.19 07:24, ikbar wrote:

> Department stored at Sort 1
>
> Let me tell the situation :
>
> Do you just need the number of checkouts, borrower information and
> department?  ----> YES
>
> Or a list with one line for each item? ----> Also YES
>
> add a timestamp
>
> My english is not good... anyway tq an advance for helping me katrin
>
>
>
> --
> Sent from: http://koha.1045719.n5.nabble.com/Koha-general-f3047918.html
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha