[Koha] SQL expertise ...

[Carl Thompson]

Can anyone help verify if these queries will work? Don't have access to
system to verify and trying to help other Koha users complete these
questions on the PLDS Annual Survey.

/* calculate annual average days of circulation of items by item type */
SELECT r.itemtype, sum(r.datetime-b.datetime)/count(r.datetime) as
*avg_days_of_circ*
FROM statistics r,  statistics b
WHERE r.type='return' and b.type='issue'
AND r.itemnumber=b.itemnumber AND r.borrowernumber=b.borrowernumber
AND year(r.datetime)=2017
GROUP BY r.itemtype;

/* the number of original circulations (not including renewals) by item
type */
SELECT itemtype, count(*) as *intitial_circ*
FROM statistics
WHERE type='issue'
AND year(datetime)=2017
GROUP BY itemtype;

/* the number of renewals by item type */
SELECT itemtype, count(*) as *renewals*
FROM statistics
WHERE type='renewal'
AND year(datetime)=2017
GROUP BY itemtype;

Thanks!

-- 
Carl Thompson <cthompson at countingopinions.com>
Counting Opinions <http://www.countingopinions.com/>
(866) 994-0904 <+18669940904>