A LESS THAN COMPREHENSIVE ASSORTMENT OF SQL TIPS AND TRICKS
QUERY ON WAYWARD SON
koha-US 2026 Annual Conference - Fayetteville, AR - August 20, 2026
Jason Robb
SEKnFind Coordinator
Southeast Kansas Library System
jrobb@sekls.org - @jrobb (Mattermost)
State Library Cards
OBLIGATORY KANSAS KOHA MAP SLIDE
Northwest
Southwest
Central
South Central
Southeast
Northeast
North
Central
TOPICS
Resources
The basics
Handy features
Examples
Upcoming enhancements
KOHA + SQL
Community Resources
Find new reports and share your work!
WIKI: KOHA REPORTS LIBRARY
Built-in tool for importing and sharing reports.
MANA
Lists tables, columns, and relationships to reference when writing reports.
SCHEMA
The source of truth for Koha, includes a section on koha’s report-writing features
MANUAL
KOHA-US
COMPILED RESOURCES
General terminology
Structured Query Language that allows us to communicate with databases
SQL
like the header row of a spreadsheet, indicate what data is stored in the rows below
COLUMNS (FIELDS)
a collection of tables where data is stored
DATABASE
contain the values/data within a column
ROWS (RECORDS)
terms used when writing SQL that do something (i.e. SELECT, FROM, WHERE, IS, IN, AND, OR, BETWEEN)
COMMANDS
the complete code you write to ask the database for info
STATEMENTS
SELECT columns
FROM table
JOIN other tables
WHERE things to filter by
BASIC QUERY STRUCTURE
....
HANDY KOHA FEATURES
BATCH OPERATIONS
Adding biblionumber, itemnumber, borrowernumber, or cardnumber to reports give the option to batch edit results
COLUMNS
Double bracket syntax can be used to rename the column while maintaining batch edit capabilities
PRETTIER NAMING
SELECT [[cardnumber|Card Number]]
PARAMETER MODIFIERS
Adds an “All” option
Must use LIKE
i.homebranch LIKE <<Library|branches:all>>
:ALL
Gives a multi-select
Must use IN
i.itype IN <<Item type|itemtypes:in>>
:IN
Feed the report a list of values
Must use IN
i.barcode IN <<Barcode list|list>>
LIST
ADDITIONAL HANDY KOHA FEATURES
LIMIT
Breaks pagination
REPEATING PARAMETERS = ONE INPUT
Using the same parameter multiple times only requires the end-user to input the choice once
AUTOCOMPLETE
Typing the table name followed by a period will give you the full list of possible columns. Defining a table alias in the FROM statement first can speed this up
...
SELECT
CASE WHEN CALLNUMRANGE
DEWEY BANDS - 3103
SELECT
CASE
WHEN items.itemcallnumber BETWEEN 'J000' AND 'J099.99999999' THEN 'J000-J099'
WHEN items.itemcallnumber BETWEEN 'J100' AND 'J199.99999999' THEN 'J100-J199'
WHEN items.itemcallnumber BETWEEN 'J200' AND 'J299.99999999' THEN 'J200-J299'
WHEN items.itemcallnumber BETWEEN 'J300' AND 'J399.99999999' THEN 'J300-J399'
WHEN items.itemcallnumber BETWEEN 'J400' AND 'J499.99999999' THEN 'J400-J499'
WHEN items.itemcallnumber BETWEEN 'J500' AND 'J599.99999999' THEN 'J500-J599'
WHEN items.itemcallnumber BETWEEN 'J600' AND 'J699.99999999' THEN 'J600-J699'
WHEN items.itemcallnumber BETWEEN 'J700' AND 'J799.99999999' THEN 'J700-J799'
WHEN items.itemcallnumber BETWEEN 'J800' AND 'J899.99999999' THEN 'J800-J899'
WHEN items.itemcallnumber BETWEEN 'J900' AND 'J999.99999999' THEN 'J900-J999'
ELSE 'Alternate categorization'
END
AS 'DeweyRange',
...
GROUP BY DeweyRange
ORDER BY DeweyRange
CASE WHEN TIMESTAMPDIFF()
PATRON AGE BANDS - 2953
SQL goes here
SELECT
CASE
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) BETWEEN 0 AND 10 THEN '0-10'
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) BETWEEN 11 AND 20 THEN '11-20'
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) BETWEEN 21 AND 30 THEN '21-30'
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) BETWEEN 31 AND 40 THEN '31-40'
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) BETWEEN 41 AND 50 THEN '41-50'
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) BETWEEN 51 AND 60 THEN '51-60'
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) BETWEEN 61 AND 70 THEN '61-70'
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) BETWEEN 71 AND 80 THEN '71-80'
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) BETWEEN 81 AND 90 THEN '81-90'
WHEN TIMESTAMPDIFF(YEAR, dateofbirth, CURDATE()) > 90 THEN '90+'
WHEN dateofbirth is NULL THEN 'no birthdate stored' END AS age_range,
COUNT(*)
FROM borrowers
WHERE branchcode = <<Choose Library|branches:all>>
GROUP BY age_range
DISSECTING CONCATENATION
CONCAT('<a href="/cgi-bin/koha/reserve/request.pl?biblionumber=', r.biblionumber, '" target="_blank">', 'View holds', '</a>') AS “View holds”
CONCAT(
'<a href="/cgi-bin/koha/reserve/request.pl?biblionumber=',
r.biblionumber,
'" target="_blank">',
'View holds',
'</a>'
) AS “View holds”
CONCAT(<p>class=’neat’</p>)
BOOTSTRAP + CONDITIONALS - 3136
SELECT
CONCAT("<h2>", biblio.title, IFNULL(biblio.subtitle, ''), "</h2>", "<p>", ExtractValue(metadata,'//datafield[@tag="300"]/*'), "</p><p class='badge text-bg-primary'>Housed at: ", branches.branchname, "</p>", CASE WHEN it.onloan IS NULL THEN '' ELSE '<br><p class="badge bg-danger"><i class="fa-solid fa-triangle-exclamation"></i> Checked out</p>' END) AS title,
...
QR CODES
CONCATENATING DATA INTO A QR CODE - 3098
SELECT b.branchcode, b.surname, b.preferred_name,
CONCAT("<img src='https://staff.seknfind.org/cgi-bin/koha/svc/barcode?type=QRcode&modulesize=3&barcode=", b.preferred_name, " ", b.surname, " - ", b.branchcode, "' />") AS qr_code
...
RENDERING IMAGES
NEW ACQUISITIONS LIST - 2818
SELECT
COALESCE(CONCAT('<img src="https://images-na.ssl-images-amazon.com/images/P/',
IF
(LEFT(TRIM(biblioitems.isbn), 3) = '978',
CONCAT(SUBSTR(TRIM(biblioitems.isbn), 4, 9),
REPLACE(MOD(11 - MOD
(CONVERT(SUBSTR(TRIM(biblioitems.isbn), 4, 1), UNSIGNED INTEGER)*10 +
CONVERT(SUBSTR(TRIM(biblioitems.isbn), 5, 1), UNSIGNED INTEGER)*9 +
CONVERT(SUBSTR(TRIM(biblioitems.isbn), 6, 1), UNSIGNED INTEGER)*8 +
CONVERT(SUBSTR(TRIM(biblioitems.isbn), 7, 1), UNSIGNED INTEGER)*7 +
CONVERT(SUBSTR(TRIM(biblioitems.isbn), 8, 1), UNSIGNED INTEGER)*6 +
CONVERT(SUBSTR(TRIM(biblioitems.isbn), 9, 1), UNSIGNED INTEGER)*5 +
CONVERT(SUBSTR(TRIM(biblioitems.isbn), 10, 1), UNSIGNED INTEGER)*4 +
CONVERT(SUBSTR(TRIM(biblioitems.isbn), 11, 1), UNSIGNED INTEGER)*3 +
CONVERT(SUBSTR(TRIM(biblioitems.isbn), 12, 1), UNSIGNED INTEGER)*2, 11
), 11), '10', 'X')
),
LEFT(TRIM(biblioitems.isbn), 10)
),
'.01.MZZZZZZZZZ.jpg">'),'<img src="https://dl.dropboxusercontent.com/s/nkun7xeysbqp4q9/noImageAvailable.png?dl=0">') AS Render
...
BUILDING FUNCTIONAL LINKS
CANCEL TRANSFER - 2660
SELECT
CONCAT('<a href=\"/cgi-bin/koha/circ/returns.pl?itemnumber=',items.itemnumber,'&canceltransfer=1&dest=ttr\" target="_blank">',"cancel transfer",'</a>') AS "transfer",
...
FORMATTING MONEY
POINT OF SALE SUMMARY REPORT - 3099
SELECT
CONCAT('<span class="fw-bold">', FORMAT(SUM(IF(c.payment_type = 'CASH', ao.amount * -1, 0)),2,'en_US'), '</span>') AS "Cash",
CONCAT('<span class="fw-bold">', FORMAT(SUM(IF(c.payment_type = 'CHECK', ao.amount * -1, 0)),2,'en_US'), '</span>') AS "Check",
CONCAT('<span class="fw-bold">', FORMAT(SUM(IF(c.payment_type = 'CREDIT', ao.amount * -1, 0)),2,'en_US'), '</span>') AS "Credit Card",
CONCAT('<span class="fw-bold">', FORMAT(SUM(IF(c.payment_type IN ('CASH', 'CHECK', 'CREDIT'), ao.amount * -1, 0)),2,'en_US'), '</span>') AS Total
...
CASE SUBSTR(MARC)
CHECKING 008 AGAINST SHELF LOCATION - 3123
SELECT
SUBSTR(ExtractValue(metadata,'//controlfield[@tag="008"]'),34,1) AS '008litform',
CASE SUBSTR(ExtractValue(metadata,'//controlfield[@tag="008"]'),34,1)
WHEN '0' THEN 'Not fiction'
WHEN '1' THEN 'Fiction'
ELSE SUBSTR(ExtractValue(metadata,'//controlfield[@tag="008"]'),34,1) END AS litformdesc,
...
GROUP BY i.itemnumber
HAVING (008litform = '0' AND loc NOT LIKE ('%NF%')
OR 008litform = '1' AND loc NOT LIKE ('%FIC%'))
CASE JSON_EXTRACT(LOGS)
WITHDRAWN STATUS MODS FROM LOGS - 3124
SELECT
CASE
WHEN JSON_EXTRACT(al.diff, '$.D.withdrawn.O') = 0 THEN ' '
WHEN JSON_EXTRACT(al.diff, '$.D.withdrawn.O') = 1 THEN 'Temporarily unavailable'
WHEN JSON_EXTRACT(al.diff, '$.D.withdrawn.O') = 2 THEN 'In repair'
WHEN JSON_EXTRACT(al.diff, '$.D.withdrawn.O') = 3 THEN 'Missing parts'
END AS Withdrawn_OldVal,
CASE
WHEN JSON_EXTRACT(al.diff, '$.D.withdrawn.N') = 0 THEN ' '
WHEN JSON_EXTRACT(al.diff, '$.D.withdrawn.N') = 1 THEN 'Temporarily unavailable'
WHEN JSON_EXTRACT(al.diff, '$.D.withdrawn.N') = 2 THEN 'In repair'
WHEN JSON_EXTRACT(al.diff, '$.D.withdrawn.N') = 3 THEN 'Missing parts'
END AS Withdrawn_NewVal,
...
MATH - PERCENTAGE NEW
NEWLY ADDED ITEM CHECK - 3000
Screenshot goes here
SELECT
SUM(CASE WHEN(b.copyrightdate >= (YEAR(CURDATE())-1) AND (i.dateaccessioned BETWEEN '2025-11-01' AND '2026-11-01')) THEN 1 ELSE 0 END) AS ItemsWithNewCopyright,
SUM(CASE WHEN(i.dateaccessioned BETWEEN '2025-11-01' AND '2026-11-01') THEN 1 ELSE 0 END) AS ItemsAddedThisYear,
COUNT(*) AS TotalItemCount,
CONCAT((((SUM(CASE WHEN(b.copyrightdate >= (YEAR(CURDATE())-1) AND (i.dateaccessioned BETWEEN '2025-11-01' AND '2026-11-01')) THEN 1 ELSE 0 END))/(COUNT(*)))*100), '%') AS "Percent of collection that's newly published",
ROUND((COUNT(*) * 0.0025), 0) AS "Minimum number of items needed for 2025-2026 Cycle"
...
...
FROM
ALIASES
PROS
CONS
Faster/less typing
Cleaner/clearer readability
Can be confusing, especially when sharing (wiki, mana, etc.)
SELECT b.title
FROM biblio b
...
SUBQUERIES
Turnover - 2796
Screenshot goes here
...
FROM
(SELECT branches.branchcode, biblocs.authorised_value, biblocs.lib
FROM branches,
(SELECT authorised_values.category, authorised_values.authorised_value, authorised_values.lib
FROM authorised_values
WHERE authorised_values.category = 'LOC'
) biblocs
) branchess
LEFT JOIN
(SELECT
items.homebranch, items.permanent_location, COUNT(items.itemnumber) AS Count_itemnumber
FROM
items
...
...
JOIN
AUTHORIZED VALUE DESCRIPTIONS
JOINING AVS MULTIPLE TIMES CLEANLY - 3081
...
LEFT JOIN authorised_values a ON (a.authorised_value=i.itemlost AND a.category='LOST')
LEFT JOIN authorised_values a2 ON (a2.authorised_value=i.notforloan AND a2.category='NOT_LOAN')
LEFT JOIN authorised_values a3 ON (a3.authorised_value=i.withdrawn AND a3.category='WITHDRAWN')
LEFT JOIN authorised_values a4 ON (a4.authorised_value=i.damaged AND a4.category='DAMAGED')
LEFT JOIN authorised_values a5 ON (a5.authorised_value=i.permanent_location AND a5.category='LOC')
...
TRIM() + JOIN
JOINING BIB AUTHOR TO CLUB NAME - 2803
...
LEFT JOIN biblio b ON (TRIM(BOTH '.' FROM(TRIM(',' FROM clubs.name))) = TRIM(TRIM(BOTH '.' FROM TRIM(BOTH ',' FROM b.author))))
...
...
WHERE
LENGTH()
FINDING BAD PHONE NUMBERS - 3056
SELECT LENGTH(smsalertnumber) as length
...
WHERE branchcode = <<Choose Library|branches>>
AND smsalertnumber <> ''
AND LENGTH(smsalertnumber) <> '12'
LIKE ‘%’ << PARAM >> ‘%’
WILDCARDING PARAMETERS - 2021
...
WHERE i.itemnotes LIKE '%' <<Enter Term>> '%'
RLIKE
PATTERN MATCHING - 2833
...
WHERE i.permanent_location RLIKE <<Shelf location grouping|LOC_GROUP>>
WHERE i.permanent_location RLIKE i.permanent_location RLIKE '0AD|0K|0AU|0EC|0NB|0SR|0WM|0LP'
...
ORDER BY
REGEX(CALLNUMS)
SORTING BY PARTS OF CALLNUM - 3141
SELECT
REGEXP_REPLACE(i.itemcallnumber, '[^a-zA-Z\\s]', '') AS base_call,
REGEXP_SUBSTR(i.itemcallnumber, '[0-9]+') + 0 AS num
...
ORDER BY base_call, num
IN THE WORKS
🪲 BZ 42667 - Passed QA
EDIT_ALL_REPORTS PERMISSION
🪲 BZ 27432 - Passed QA
ADD REPORTS ‘RUN’ ACTION TO LOGS
🪲 BZ 16631 - Pushed to main
LIMIT REPORT VISIBILITY BY BRANCH
WORTH A LOOK
🪲 BZ 42488 New
SUBQUERY
PREVENTION
🪲 BZ 42585 Needs Signoff
WARN FOR POTENTIALLY DANGEROUS REPORTS
TRANSFER REPORT OWNERSHIP
Jason Robb
SEKnFind Coordinator
Southeast Kansas Library System
jrobb@sekls.org - @jrobb (Mattermost)
THANKS