A well-configured Koha installation contains an enormous amount of useful data. Circulation transactions, patron activity, acquisition costs, collection growth, overdue items, fines, lost books, and much more are stored in the database.

The problem is that having data and actually using it are two different things.

For a college or university library, the Reports module is one of the most powerful parts of Koha. While the guided report wizard is useful for many routine tasks, SQL reports allow library administrators to build exactly the reports their institution needs.

Koha stores its operational data in a MySQL/MariaDB database and allows authorized staff to create custom reports using SQL. The official Koha documentation also points administrators toward the community SQL Reports Library and the Koha database schema when creating custom reports.

This article provides 15 practical Koha SQL reports that I believe every college or university library should maintain.

Important: Always test SQL reports on a development/staging database before using them on a production system. These queries are SELECT statements and do not modify data, but database structure and local customisations can vary between Koha versions.

Before You Start

Go to:

Staff Client → More → Reports → Create from SQL

You need appropriate report permissions, such as the create_reports permission or superlibrarian access to create SQL reports. Koha also supports runtime parameters such as dates, libraries, item types, patron categories, and lists, which can make reports considerably more useful.

The examples below use common Koha tables, including:

• biblio – bibliographic records
• biblioitems – bibliographic/item-level information
• items – individual copies
• borrowers – patron records
• issues – currently checked-out items
• old_issues – completed checkout history
• statistics – circulation and other transaction statistics
• accountlines – patron financial transactions
• account_offsets – links between financial debits and credits
• aqorders – acquisition order lines
• branches – library/branch information

The current Koha schema documents these relationships and fields.

1. Overdue Books Report

The overdue report is probably the first report every circulation section needs.

Koha already provides a built-in Overdues report, which lists items currently overdue and can be filtered and exported. For libraries that need customized output, however, an SQL report gives much greater control.

SQL Query

SELECT
    b.cardnumber AS 'Patron ID',
    CONCAT(b.firstname, ' ', b.surname) AS 'Patron Name',
    b.categorycode AS 'Category',
    i.barcode AS 'Barcode',
    bi.title AS 'Title',
    bi.author AS 'Author',
    i.itemcallnumber AS 'Call Number',
    DATE_FORMAT(iss.issuedate, '%d-%m-%Y') AS 'Issue Date',
    DATE_FORMAT(iss.date_due, '%d-%m-%Y') AS 'Due Date',
    DATEDIFF(CURDATE(), DATE(iss.date_due)) AS 'Days Overdue',
    i.homebranch AS 'Library'
FROM issues iss
JOIN borrowers b
    ON iss.borrowernumber = b.borrowernumber
JOIN items i
    ON iss.itemnumber = i.itemnumber
JOIN biblio bi
    ON i.biblionumber = bi.biblionumber
WHERE iss.date_due < NOW()
ORDER BY Days Overdue DESC;

Why is it useful

This report can be used for:

• overdue notices
• department-wise follow-up
• identifying seriously overdue books
• recovery drives
• circulation monitoring

For a large university library, consider adding filters for library, patron category or number of overdue days.

2. Lost Books Report

Lost items are especially important in academic libraries because the library needs to know not only which books are lost, but also their financial value.

Koha’s item record contains both itemlost and itemlost_on, while price stores the purchase price and replacementprice stores the replacement cost.

SQL Query

SELECT
    i.barcode AS 'Barcode',
    bi.title AS 'Title',
    bi.author AS 'Author',
    i.itemcallnumber AS 'Call Number',
    i.homebranch AS 'Library',
    i.price AS 'Purchase Price',
    i.replacementprice AS 'Replacement Price',
    DATE_FORMAT(i.itemlost_on, '%d-%m-%Y') AS 'Lost Date'
FROM items i
JOIN biblio bi
    ON i.biblionumber = bi.biblionumber
WHERE i.itemlost > 0
ORDER BY i.itemlost_on DESC;

Useful addition

You can calculate the total replacement value:

SELECT
    COUNT(*) AS 'Lost Items',
    SUM(COALESCE(i.replacementprice, i.price, 0)) AS 'Total Replacement Value'
FROM items i
WHERE i.itemlost > 0;

3. Most Issued Titles

Knowing which books are used most frequently helps librarians make decisions about:

• additional copies
• replacement
• course reserves
• collection development
• weeding
• budgeting

Koha’s statistics documentation includes a “Most circulated items” report, and bibliographic records also maintain total issue counts.

SQL Query

SELECT
    b.biblionumber AS 'Biblio Number',
    b.title AS 'Title',
    b.author AS 'Author',
    bi.totalissues AS 'Total Issues'
FROM biblio b
JOIN biblioitems bi
    ON b.biblionumber = bi.biblionumber
ORDER BY bi.totalissues DESC
LIMIT 100;

Tip

For annual reporting, a better approach is to use the statistics table and restrict the query to a specific date range. This lets you answer questions such as:

Which were the 50 most issued books during the 2025–26 academic year?

4. Most Active Users

A university library should know which patrons actually use its resources.

This report ranks patrons according to their circulation activity.

SQL Query

SELECT
    b.cardnumber AS 'Patron ID',
    CONCAT(b.firstname, ' ', b.surname) AS 'Patron Name',
    b.categorycode AS 'Category',
    COUNT(*) AS 'Total Issues'
FROM old_issues oi
JOIN borrowers b
    ON oi.borrowernumber = b.borrowernumber
GROUP BY
    b.borrowernumber,
    b.cardnumber,
    b.firstname,
    b.surname,
    b.categorycode
ORDER BY `Total Issues` DESC
LIMIT 100;

This is particularly useful when preparing:

• annual library statistics
• user engagement reports
• departmental comparisons
• library usage studies

5. Inactive Users

Koha already provides a “Patrons with no checkouts” report.

For an institution, however, you may want a more useful definition of inactivity, such as:

Patrons who have never borrowed a book.

SQL Query

SELECT
    b.cardnumber AS 'Patron ID',
    CONCAT(b.firstname, ' ', b.surname) AS 'Patron Name',
    b.categorycode AS 'Category',
    b.branchcode AS 'Library',
    DATE_FORMAT(b.dateenrolled, '%d-%m-%Y') AS 'Enrollment Date'
FROM borrowers b
LEFT JOIN old_issues oi
    ON b.borrowernumber = oi.borrowernumber
LEFT JOIN issues iss
    ON b.borrowernumber = iss.borrowernumber
WHERE oi.borrowernumber IS NULL
  AND iss.borrowernumber IS NULL
ORDER BY b.surname, b.firstname;

More useful version: no activity for 12 months

SELECT
    b.cardnumber AS 'Patron ID',
    CONCAT(b.firstname, ' ', b.surname) AS 'Patron Name',
    b.categorycode AS 'Category',
    b.branchcode AS 'Library',
    MAX(oi.issuedate) AS 'Last Issue'
FROM borrowers b
LEFT JOIN old_issues oi
    ON b.borrowernumber = oi.borrowernumber
GROUP BY
    b.borrowernumber,
    b.cardnumber,
    b.firstname,
    b.surname,
    b.categorycode,
    b.branchcode
HAVING
    MAX(oi.issuedate) IS NULL
    OR MAX(oi.issuedate) < DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
ORDER BY `Last Issue`;

6. Department-wise Circulation

This is particularly valuable in a university environment.

A library administrator may want to know:

• Which department uses the library the most?
• Which departments have low circulation?
• Which departments need collection development?
• Which academic programs generate the most library use?

There is one important caveat here.

Koha does not universally have a standard department column in the patron record. Many institutions store department information using patron attributes. Koha supports borrower_attributes, so the exact query depends on how your institution has configured patron attributes.

Assuming your patron attribute code is DEPARTMENT:

SQL Query

SELECT
    ba.attribute AS 'Department',
    COUNT(*) AS 'Total Issues'
FROM old_issues oi
JOIN borrower_attributes ba
    ON oi.borrowernumber = ba.borrowernumber
WHERE ba.code = 'DEPARTMENT'
GROUP BY ba.attribute
ORDER BY `Total Issues` DESC;

If your institution stores the department directly in a separate field, adjust the query accordingly.

7. Collection Growth Report

Collection growth is an important annual library statistic.

This report shows the number of items added to the collection by year.

Koha stores the date an item was acquired or added in items.dateaccessioned.

SQL Query

SELECT
    YEAR(dateaccessioned) AS 'Year',
    COUNT(*) AS 'Items Added'
FROM items
WHERE dateaccessioned IS NOT NULL
GROUP BY YEAR(dateaccessioned)
ORDER BY YEAR(dateaccessioned);

Collection growth by the library

SELECT
    i.homebranch AS 'Library',
    YEAR(i.dateaccessioned) AS 'Year',
    COUNT(*) AS 'Items Added'
FROM items i
WHERE i.dateaccessioned IS NOT NULL
GROUP BY
    i.homebranch,
    YEAR(i.dateaccessioned)
ORDER BY
    YEAR(i.dateaccessioned),
    i.homebranch;

This can be exported to Excel and converted into an annual collection-growth chart.

8. Acquisition Expenditure

This is one of the most important reports for a university library because management frequently asks the inevitable question:

“How much did the library actually spend?”

Koha’s aqorders The table stores acquisition information, including quantity, receiving date, unit price, tax-inclusive/exclusive prices, and quantity received.

Annual acquisition expenditure

SELECT
    YEAR(aq.datereceived) AS 'Year',
    COUNT(*) AS 'Order Lines',
    SUM(
        COALESCE(aq.unitprice_tax_included, aq.unitprice, 0)
        * COALESCE(aq.quantityreceived, 0)
    ) AS 'Expenditure'
FROM aqorders aq
WHERE aq.datereceived IS NOT NULL
GROUP BY YEAR(aq.datereceived)
ORDER BY YEAR(aq.datereceived);

Financial-year version

For institutions following the Indian financial year:

SELECT
    CASE
        WHEN MONTH(aq.datereceived) >= 4
        THEN CONCAT(YEAR(aq.datereceived), '-', YEAR(aq.datereceived) + 1)
        ELSE CONCAT(YEAR(aq.datereceived) - 1, '-', YEAR(aq.datereceived))
    END AS 'Financial Year',

    SUM(
        COALESCE(aq.unitprice_tax_included, aq.unitprice, 0)
        * COALESCE(aq.quantityreceived, 0)
    ) AS 'Expenditure'

FROM aqorders aq

WHERE aq.datereceived IS NOT NULL

GROUP BY
    CASE
        WHEN MONTH(aq.datereceived) >= 4
        THEN CONCAT(YEAR(aq.datereceived), '-', YEAR(aq.datereceived) + 1)
        ELSE CONCAT(YEAR(aq.datereceived) - 1, '-', YEAR(aq.datereceived))
    END

ORDER BY `Financial Year`;

Note: For audited financial reporting, always reconcile the SQL result with Koha’s Acquisitions module and institutional accounting records. Discounts, taxes, currencies, freight, cancellations, and partial receipts can affect how expenditure should be interpreted.

9. Unused Books

One of the most useful collection-development reports is:

Which books have never been issued?

Koha already provides an “Items with no checkouts” report.

SQL Query

SELECT
    i.barcode AS 'Barcode',
    b.title AS 'Title',
    b.author AS 'Author',
    i.itemcallnumber AS 'Call Number',
    i.homebranch AS 'Library',
    DATE_FORMAT(i.dateaccessioned, '%d-%m-%Y') AS 'Accession Date',
    i.price AS 'Price'
FROM items i
JOIN biblio b
    ON i.biblionumber = b.biblionumber
WHERE i.datelastborrowed IS NULL
  AND i.itemlost = 0
  AND i.withdrawn = 0
ORDER BY i.dateaccessioned;

Books not used for five years

SELECT
    i.barcode AS 'Barcode',
    b.title AS 'Title',
    b.author AS 'Author',
    i.itemcallnumber AS 'Call Number',
    i.homebranch AS 'Library',
    i.datelastborrowed AS 'Last Borrowed',
    i.price AS 'Price'
FROM items i
JOIN biblio b
    ON i.biblionumber = b.biblionumber
WHERE
    (i.datelastborrowed IS NULL
     OR i.datelastborrowed < DATE_SUB(CURDATE(), INTERVAL 5 YEAR))
    AND i.itemlost = 0
    AND i.withdrawn = 0
ORDER BY i.datelastborrowed;

This is useful for collection evaluation and weeding, but should never be used as the sole criterion for withdrawing a book. A rarely borrowed reference book may still be essential.

10. Fine Collection

Koha’s financial system stores patron charges in accountlines. The current schema includes debit/credit type codes, amounts and outstanding balances, while account_offsets it records how credits and debits affect account balances.

There are two different questions here:

1. How much was charged as overdue fines?
2. How much was actually collected?

They should not be confused.

Overdue fines charged

SELECT
    DATE(al.date) AS 'Date',
    COUNT(*) AS 'Transactions',
    SUM(al.amount) AS 'Fine Charged'
FROM accountlines al
WHERE al.debit_type_code = 'OVERDUE'
GROUP BY DATE(al.date)
ORDER BY DATE(al.date) DESC;

Outstanding overdue fines

SELECT
    COUNT(*) AS 'Fine Transactions',
    SUM(al.amountoutstanding) AS 'Outstanding Fine'
FROM accountlines al
WHERE
    al.debit_type_code = 'OVERDUE'
    AND al.amountoutstanding > 0;

For a financial audit, use account_offsets rather than simply assuming that amount - amountoutstanding equals money collected. Koha’s offset system explicitly tracks changes between credits and debits and supports different offset types.

11. Damaged/Lost Materials

A university library should maintain a separate report for damaged and lost materials.

Koha’s items table stores damaged, itemlost, damaged_on and itemlost_on. These status values are controlled by authorised values rather than necessarily being a simple yes/no field.

SQL Query

SELECT
    CASE
        WHEN i.itemlost > 0 THEN 'Lost'
        WHEN i.damaged > 0 THEN 'Damaged'
    END AS 'Status',
    i.barcode AS 'Barcode',
    b.title AS 'Title',
    b.author AS 'Author',
    i.itemcallnumber AS 'Call Number',
    i.price AS 'Purchase Price',
    i.replacementprice AS 'Replacement Price',
    i.homebranch AS 'Library',
    i.itemlost_on AS 'Lost Date',
    i.damaged_on AS 'Damaged Date'
FROM items i
JOIN biblio b
    ON i.biblionumber = b.biblionumber
WHERE
    i.itemlost > 0
    OR i.damaged > 0
ORDER BY
    Status,
    b.title;

Total financial value

SELECT
    SUM(
        CASE
            WHEN i.itemlost > 0
            THEN COALESCE(i.replacementprice, i.price, 0)
            ELSE 0
        END
    ) AS 'Lost Replacement Value',

    SUM(
        CASE
            WHEN i.damaged > 0
            THEN COALESCE(i.price, 0)
            ELSE 0
        END
    ) AS 'Damaged Book Value'

FROM items i
WHERE
    i.itemlost > 0
    OR i.damaged > 0;

12. Accession Register with Book Price

The accession register remains one of the most important reports in a college or university library.

A useful accession register should contain:

• accession/barcode number
• title
• author
• publisher
• publication year
• call number
• accession date
• price
• library
• status

Koha’s item table stores the barcode, accession date, and purchase price, while bibliographic information is stored in biblio and biblioitems.

SQL Query

SELECT
    i.barcode AS 'Accession Number',
    b.title AS 'Title',
    b.author AS 'Author',
    bi.publishercode AS 'Publisher',
    bi.publicationyear AS 'Publication Year',
    i.itemcallnumber AS 'Call Number',
    DATE_FORMAT(i.dateaccessioned, '%d-%m-%Y') AS 'Accession Date',
    i.price AS 'Price',
    i.homebranch AS 'Library',
    i.ccode AS 'Collection Code'
FROM items i
JOIN biblio b
    ON i.biblionumber = b.biblionumber
LEFT JOIN biblioitems bi
    ON i.biblioitemnumber = bi.biblioitemnumber
ORDER BY
    i.dateaccessioned,
    i.barcode;

Accession register for a date range

For example, to create a report for a particular financial year:

SELECT
    i.barcode AS 'Accession Number',
    b.title AS 'Title',
    b.author AS 'Author',
    bi.publishercode AS 'Publisher',
    i.itemcallnumber AS 'Call Number',
    DATE_FORMAT(i.dateaccessioned, '%d-%m-%Y') AS 'Accession Date',
    i.price AS 'Price',
    i.homebranch AS 'Library'
FROM items i
JOIN biblio b
    ON i.biblionumber = b.biblionumber
LEFT JOIN biblioitems bi
    ON i.biblioitemnumber = bi.biblioitemnumber
WHERE i.dateaccessioned BETWEEN '2025-04-01' AND '2026-03-31'
ORDER BY i.dateaccessioned, i.barcode;

13. Patrons with Outstanding Fine

This is different from the Fine Collection report.

Here, the question is:

Which patrons currently owe money to the library?

SQL Query

SELECT
    b.cardnumber AS 'Patron ID',
    CONCAT(b.firstname, ' ', b.surname) AS 'Patron Name',
    b.categorycode AS 'Category',
    b.branchcode AS 'Library',
    ROUND(SUM(al.amountoutstanding), 2) AS 'Outstanding Amount'
FROM borrowers b
JOIN accountlines al
    ON b.borrowernumber = al.borrowernumber
WHERE al.amountoutstanding > 0
GROUP BY
    b.borrowernumber,
    b.cardnumber,
    b.firstname,
    b.surname,
    b.categorycode,
    b.branchcode
HAVING SUM(al.amountoutstanding) > 0
ORDER BY `Outstanding Amount` DESC;

Only patrons owing more than ₹100

...
HAVING SUM(al.amountoutstanding) > 100
ORDER BY `Outstanding Amount` DESC;

This can be particularly useful before:

• issuing clearance certificates
• graduation
• student exit formalities
• hostel clearance
• transfer certificates
• annual account reconciliation

14. Patrons with Long Overdue Books

Not every overdue book requires the same level of intervention.

A book overdue by three days is very different from a book overdue by 180 days.

This report identifies patrons with significantly overdue material.

Books overdue by more than 30 days

SELECT
    b.cardnumber AS 'Patron ID',
    CONCAT(b.firstname, ' ', b.surname) AS 'Patron Name',
    b.categorycode AS 'Category',
    i.barcode AS 'Barcode',
    bi.title AS 'Title',
    DATE_FORMAT(iss.date_due, '%d-%m-%Y') AS 'Due Date',
    DATEDIFF(CURDATE(), DATE(iss.date_due)) AS 'Days Overdue',
    i.homebranch AS 'Library'
FROM issues iss
JOIN borrowers b
    ON iss.borrowernumber = b.borrowernumber
JOIN items i
    ON iss.itemnumber = i.itemnumber
JOIN biblio bi
    ON i.biblionumber = bi.biblionumber
WHERE
    iss.date_due < DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY
    `Days Overdue` DESC;

Change 30 to 60, 90, 180, etc., depending on your library policy.

This is particularly useful for identifying cases requiring direct intervention rather than endlessly sending automated overdue notices into the digital void.

15. Daily Circulation Report

Finally, every academic library should maintain a daily circulation report.

This can answer:

• How many books were issued today?
• How many were returned?
• How many renewals occurred?
• Which branch handled the most transactions?
• How much circulation occurred during a particular period?

Koha records transaction information in the statistics table. The current schema identifies transaction types such as issue, return, renew, payment and writeoff, along with transaction date/time, branch, item, patron, and item type.

Daily circulation summary

SELECT
    DATE(s.datetime) AS 'Date',

    SUM(CASE
        WHEN s.type = 'issue' THEN 1
        ELSE 0
    END) AS 'Issues',

    SUM(CASE
        WHEN s.type = 'return' THEN 1
        ELSE 0
    END) AS 'Returns',

    SUM(CASE
        WHEN s.type = 'renew' THEN 1
        ELSE 0
    END) AS 'Renewals'

FROM statistics s

WHERE DATE(s.datetime) = CURDATE()

GROUP BY DATE(s.datetime);

Daily circulation by the library

SELECT
    DATE(s.datetime) AS 'Date',
    s.branch AS 'Library',

    SUM(CASE
        WHEN s.type = 'issue' THEN 1
        ELSE 0
    END) AS 'Issues',

    SUM(CASE
        WHEN s.type = 'return' THEN 1
        ELSE 0
    END) AS 'Returns',

    SUM(CASE
        WHEN s.type = 'renew' THEN 1
        ELSE 0
    END) AS 'Renewals'

FROM statistics s

WHERE DATE(s.datetime) = CURDATE()

GROUP BY
    DATE(s.datetime),
    s.branch

ORDER BY s.branch;

Bonus: Monthly Circulation Statistics

Once you have the daily circulation report, a monthly version is easy.

SELECT
    YEAR(s.datetime) AS 'Year',
    MONTH(s.datetime) AS 'Month',

    SUM(CASE
        WHEN s.type = 'issue' THEN 1
        ELSE 0
    END) AS 'Issues',

    SUM(CASE
        WHEN s.type = 'return' THEN 1
        ELSE 0
    END) AS 'Returns',

    SUM(CASE
        WHEN s.type = 'renew' THEN 1
        ELSE 0
    END) AS 'Renewals'

FROM statistics s

GROUP BY
    YEAR(s.datetime),
    MONTH(s.datetime)

ORDER BY
    YEAR(s.datetime),
    MONTH(s.datetime);

This is useful for annual reports and institutional statistics.

Important: Protect Patron Privacy

Not every report should be made public.

Koha’s documentation specifically warns that reports containing patron information should generally not be made public because public reports can potentially be accessed through the JSON reports service without normal authentication.

Reports containing:

• names
• card numbers
• fines
• borrowing history
• overdue books
• departments
• email addresses

should therefore be restricted to authorized staff.

In particular, avoid creating public reports that expose complete patron circulation histories.

Making These Reports More Useful with Runtime Parameters

Hard-coded SQL is useful, but parameterized reports are much better.

For example:

WHERE iss.date_due < <<Due before|date>>

or:

WHERE i.homebranch = <<Library|branches>>

Koha supports runtime parameters for dates, libraries, item types, patron categories and lists.

This allows the same report to be reused by staff instead of maintaining twenty slightly different SQL queries.

For example, an overdue report could ask the user to select:

Library → Patron Category → Due Date → Item Type

and then generate the required result.

To Sum Up!

Koha already provides many useful statistical and guided reports, including reports for circulation, lost items, acquisitions, most-circulated items, patrons with no checkouts, and items with no checkouts.

But the real power of Koha becomes apparent when the library starts asking questions specific to its institutional requirements.

For a college or university library, SQL reports can turn the Koha database into a practical library management information system.

Instead of simply asking:

“How many books were issued?”

You can ask:

• Which departments are using the library?
• Which books are actually being used?
• Which books have never been issued?
• Which patrons have stopped using the library?
• How much has the library spent on books?
• How much money is currently outstanding?
• Which books are seriously overdue?
• How much of the collection was added this year?
• Which titles should receive additional copies?
• What is the financial value of lost material?

That is where SQL reporting becomes much more than a technical feature. It becomes a tool for evidence-based library management.

A final note about versions

Koha’s database schema changes over time. Before deploying any query in production, compare the tables and fields against the schema for your installed Koha version. The official schema documentation is the safest reference for confirming field names and relationships.

If you are running a customized Koha installation, also test reports after major upgrades. A report that depends on a local patron attribute, authorized value, or customized field will naturally require adjustment.