Statistical reports

From Koha Wiki
Jump to navigation Jump to search

This is the page that relates to statistical reports.

The following SQL statements have been written by Koha users world-wide. Keep in mind that not all reports have been tested and should be read thoroughly before applying them to your own system.

Return to the SQL Reports Library

Shows the total number of items circulated from a branch other than the owning branch

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Statistical (Circulation)
  • Purpose: Shows the total number of items circulated from a branch other than the owning branch
  • Status: Complete
  SELECT count(*) as total 
  FROM statistics 
  LEFT JOIN items on (statistics.itemnumber = items.itemnumber) 
  WHERE statistics.branch != items.homebranch AND statistics.datetime BETWEEN <<Between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>>

Activity by itemtype in Date Range (Excludes itemtypes: 'CR', 'EM' and 'SAUORDER' which are our Periodicals, Electronic Resources, and Orders item types)

  • Developer: Joseph Alway
  • Module: Statistical (Circulation)
  • Purpose: Activity by itemtype in Date Range (Excludes itemtypes: 'CR', 'EM' and 'SAUORDER' which are our Periodicals, Electronic Resources, and Orders item types)
  • Status: Complete
SELECT 
    itemtypes.itemtype,
    itemtypes.description,
    COALESCE(circ_issue_data.`Circulation Count`, 0) AS `Circulation Count`,
    COALESCE(circ_renewal_data.`Renewal Count`, 0) AS `Renewal Count`,
    COALESCE(circ_localuse_data.`Local Use Count`, 0) AS `Local Use Count`
FROM 
    itemtypes
LEFT JOIN 
    (
        -- Count of original circulations
        SELECT
            statistics.itemtype,
            COUNT(statistics.datetime) AS `Circulation Count`
        FROM 
            statistics
        WHERE
            statistics.type = 'issue'
            AND DATE(statistics.datetime) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
        GROUP BY 
            statistics.itemtype
    ) AS circ_issue_data
ON 
    itemtypes.itemtype = circ_issue_data.itemtype
LEFT JOIN 
    (
        -- Count of renewal circulations
        SELECT
            statistics.itemtype,
            COUNT(statistics.datetime) AS `Renewal Count`
        FROM 
            statistics
        WHERE 
            statistics.type = 'renew'
            AND DATE(statistics.datetime) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
        GROUP BY 
            statistics.itemtype
    ) AS circ_renewal_data
ON 
    itemtypes.itemtype = circ_renewal_data.itemtype
LEFT JOIN 
    (
        -- Count of local use circulations
        SELECT
            statistics.itemtype,
            COUNT(statistics.datetime) AS `Local Use Count`
        FROM 
            statistics
        WHERE 
            statistics.type = 'localuse'
            AND DATE(statistics.datetime) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
        GROUP BY 
            statistics.itemtype
    ) AS circ_localuse_data
ON 
    itemtypes.itemtype = circ_localuse_data.itemtype
WHERE 
    itemtypes.itemtype NOT IN ('CR', 'SAEM', 'SAUORDER')

UNION ALL

-- Total line
SELECT
    '' AS `itemtype`,
    'Report Totals:' AS `description`,
    SUM(circ_issue_data.`Circulation Count`) AS `Circulation Count`,
    SUM(circ_renewal_data.`Renewal Count`) AS `Renewal Count`,
    SUM(circ_localuse_data.`Local Use Count`) AS `Local Use Count`
FROM 
    itemtypes
LEFT JOIN 
    (
        -- Count of original circulations
        SELECT
            statistics.itemtype,
            COUNT(statistics.datetime) AS `Circulation Count`
        FROM 
            statistics
        WHERE
            statistics.type = 'issue'
            AND DATE(statistics.datetime) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
        GROUP BY 
            statistics.itemtype
    ) AS circ_issue_data
ON 
    itemtypes.itemtype = circ_issue_data.itemtype
LEFT JOIN 
    (
        -- Count of renewal circulations
        SELECT
            statistics.itemtype,
            COUNT(statistics.datetime) AS `Renewal Count`
        FROM 
            statistics
        WHERE 
            statistics.type = 'renew'
            AND DATE(statistics.datetime) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
        GROUP BY 
            statistics.itemtype
    ) AS circ_renewal_data
ON 
    itemtypes.itemtype = circ_renewal_data.itemtype
LEFT JOIN 
    (
        -- Count of local use circulations
        SELECT
            statistics.itemtype,
            COUNT(statistics.datetime) AS `Local Use Count`
        FROM 
            statistics
        WHERE 
            statistics.type = 'localuse'
            AND DATE(statistics.datetime) BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
        GROUP BY 
            statistics.itemtype
    ) AS circ_localuse_data
ON 
    itemtypes.itemtype = circ_localuse_data.itemtype
WHERE 
    itemtypes.itemtype NOT IN ('CR', 'SAEM', 'SAUORDER')

ORDER BY 
    `itemtype` = '', -- Prioritize rows where itemtype is not empty
    `itemtype`

Patrons with most checkouts in date range

  • Developer: Nicole C. Baratta, ByWater Solutions
  • Module: Statistical (Circulation)
  • Purpose: This report will show the top 20 patrons who have checked out the most in a specific time period.
  • Status: Complete
select concat(b.surname,', ',b.firstname) as name, 
       count(s.borrowernumber) as checkouts 
from statistics s 
left join borrowers b 
using (borrowernumber) 
where s.datetime between <<Top checkouts between (yyyy-mm-dd)|date>> and <<and (yyyy-mm-dd)|date>> 
group by s.borrowernumber 
order by count(s.borrowernumber) desc 
limit 20

New materials added

  • Developer: Sharon Moreland
  • Module: Statistical (Circulation)
  • Purpose: New materials added
  • Status: Complete
  SELECT count(i.biblionumber) as added, i.itype, i.homebranch, i.location from items i 
  WHERE YEAR(i.dateaccessioned) = <<Year accessioned (yyyy)>> AND MONTH(i.dateaccessioned) = <<Month accessioned (mm)>> 
  GROUP BY i.homebranch,i.itype,i.location 
  ORDER BY i.homebranch,i.itype,i.location ASC

Inactive Borrowers

  • Developer: Jonathan Field
  • Module: Statistical (Circulation, Reports)
  • Purpose: List of Borrowers who have not used the library within a given period
  • Status: Complete
SELECT DISTINCT borrowers.surname, borrowers.firstname, borrowers.cardnumber, borrowers.email 
FROM borrowers
WHERE NOT EXISTS (SELECT borrowernumber FROM statistics WHERE borrowers.borrowernumber = borrowernumber AND statistics.datetime >= 'YYYY-MM-DD')

Number of links clicked in the last month

  • Developer: Liz Rea, Catalyst IT for Albany Senior High School, New Zealand
  • Module: Statistical
  • Purpose: Count of links clicked in the last month
  • Status: Complete
SELECT count(*) FROM linktracker WHERE timeclicked > DATE_SUB(now(), INTERVAL 1 MONTH)

List of links clicked in the last month

  • Developer: Liz Rea, Catalyst IT for Albany Senior High School, New Zealand
  • Module: Statistical
  • Purpose: List of links clicked in the last month
  • Status: Complete
SELECT count(url) AS 'times', url FROM linktracker WHERE timeclicked > DATE_SUB(now(), INTERVAL 1 MONTH) GROUP BY url

Statistic for daily catalogers achievement in date range for bib records

  • Developer: Karam Qubsi
  • Module: Cataloging
  • Purpose: Statistic for daily catalogers achievement in date range for bib records (you can change the date range I make it for the whole 2014 year in this example )
  • Status: Complete
SELECT DATE_FORMAT(I.timestamp, '%d-%c-%Y') AS Date,
B.userid AS Staff,
count(I.timestamp) AS Count
FROM action_logs I
LEFT JOIN borrowers B
ON I.user=B.borrowernumber
WHERE I.module='CATALOGUING' AND I.action='ADD' AND date(I.timestamp) BETWEEN '2014-01-01' AND '2014-12-31' 
 AND I.info='biblio'
GROUP BY Date,B.userid
ORDER BY DATE(timestamp) DESC

Statistic for daily catalogers achievement in date range for Item records

  • Developer: Karam Qubsi
  • Module: Cataloging
  • Purpose: Statistic for daily catalogers achievement in date range for item records (you can change the date range I make it for the whole 2014 year in this example )
  • Status: Complete
SELECT DATE_FORMAT(I.timestamp, '%d-%c-%Y') AS Date,
B.userid AS Staff,
count(I.timestamp) AS Count
FROM action_logs I
LEFT JOIN borrowers B
ON I.user=B.borrowernumber
WHERE I.module='CATALOGUING' AND I.action='ADD' AND date(I.timestamp) BETWEEN '2014-01-01' AND '2014-12-31' 
 AND I.info='item'
GROUP BY Date,B.userid
ORDER BY DATE(timestamp) DESC

Percentage lost broken down by homebranch

  • Developer: Barton Chittenden
  • Module: Cataloging
  • Purpose: Find out if certain branches are losing more items
  • Status: Complete
select
  homebranch,
  count(homebranch),
  sum( IF(itemlost=0,0,1) ) as 'Number Lost',
  (sum( IF(itemlost=0,0,1) ) * 100 / count(homebranch)) as 'Percentage lost'
from items
group by homebranch
having count(homebranch) > 0
order by (sum( IF(itemlost=0,0,1) ) * 100 / count(homebranch))

Statistics for college loans

  • Developer: Jussef Martínez
  • Module: Statistical
  • Purpose: Count the number of loans made by users of a faculty.
  • Status: Complete
SELECT
  u.sort1 AS 'Department',
  COUNT(lg.object) AS 'No. Loans'
  
FROM action_logs lg

  LEFT JOIN borrowers u ON (lg.object = u.borrowernumber)
  LEFT JOIN categories k ON (u.categorycode = k.categorycode )
  
WHERE
  lg.module = 'circulation' and action = 'issue' AND
  DATE(lg.timestamp) BETWEEN <<fecha inicial|date>> AND <<fecha final|date>>

GROUP BY u.sort1
ORDER BY u.sort2 ASC

Librarians activity

  • Developer: Josef Moravec
  • Module: Statistical
  • Purpose: Count the actions performed by librarians in particular modules of Koha
  • Status: Complete
SELECT 
  CONCAT_WS(" ", b.firstname, b.surname) AS name, 
  al.module, 
  COUNT(*) AS count

FROM action_logs al
  JOIN borrowers b ON al.user = b.borrowernumber

WHERE 
  timestamp >= <<From|date>> 
  AND timestamp <= DATE_ADD(<<To|date>>, INTERVAL 1 DAY)  
  AND user != 0

GROUP BY al.user, al.module

Shows details of statistics for a given shelving location in a date range

  • Developer: Andrew Fuerste-Henry, ByWater Solutions
  • Module: Statistical (Circulation)
  • Purpose: Shows details of statistics for a given shelving location in a date range
  • Status: Complete
SELECT i.barcode, b.title, s.type, s.location, s.datetime
FROM statistics s left join items i using (itemnumber) left join biblio b using (biblionumber)
WHERE s.location=<<Shelving Location|loc>> and s.datetime between <<Start Date|date>> and <<End Date|date>>
ORDER BY s.datetime desc

List count of 'genderwise statistical data' of patrons who are enrolled to library

  • Developer: Pankaj Kumar Sharma
  • Module: Statistical (Patron)
  • Purpose: List count of 'genderwise statistical data' of patrons who are enrolled to library
  • Status: Completed
  • Works/Tesed with: Koha Version-18.05.04.000, MySQL Version-14.14
Select count(borrowernumber) as 'Genderwise Enrollment',sex as Gender from borrowers
where borrowernumber!=1 
group by Gender

List count of 'citywise gender statistical data' of patrons who are enrolled to library

  • Developer: Pankaj Kumar Sharma
  • Module: Statistical (Patron)
  • Purpose: List count of 'citywise gender statistical data' of patrons who are enrolled to library
  • Status: Completed
  • Works/Tesed with: Koha Version-18.05.04.000, MySQL Version-14.14
Select count(borrowernumber) as 'Genderwise Enrollment',city,sex as Gender from borrowers
where borrowernumber!=1 
group by city,Gender order by city
Retrieved from "https://wiki.koha-community.org/w/index.php?title=Statistical_reports&oldid=38530"