This should solve #1 (and thus maybe #2?). I'm reading both as "percentage of the items in this collection that actually moved during a date range." The major potential flaw in this report is that it counts the number of items CURRENTLY in the collection, so if I recently purchased or weeded a bunch of DVDs, the percentages from an older date range wouldn't really be meaningful. SELECT x.ccode, x.issues AS 'Items circulating', y.itemcount AS 'Collection total', (x.issues * 100 / y.itemcount) AS 'Percentage of total items' FROM (SELECT i.ccode, COUNT(DISTINCT itemnumber) AS 'issues' FROM statistics s JOIN items i USING (itemnumber) WHERE s.datetime BETWEEN <<Checked out BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND s.type='issue' GROUP BY i.ccode) x LEFT JOIN (SELECT ccode, COUNT(itemnumber) AS 'itemcount' FROM items GROUP BY ccode) y USING (ccode) Katelyn Browne Middle/High School Librarian Capital City Public Charter School 100 Peabody Street NW Washington, DC 20011 (202) 387-0309 x1745 kbrowne@ccpcs.org http://www.ccpcs.org/library/ On Thu, May 15, 2014 at 10:51 PM, Nicole Engard <nengard@gmail.com> wrote:
I'm looking for some help with reports that require more math than my brain can handle.
#1: circulation by % coll code within a date range [50% of DVDs circulated]
I have this one:
http://wiki.koha-community.org/wiki/SQL_Reports_Library#Percentage_of_circs_... I'm not sure how to alter it to show the percentage of the DVDs in the collection instead of the percentage of all check outs.
#2: DVDs circulating 50% of total DVDs where DVD is a collection code
This might be the same as the above ... unless you all read it differently than me.
Thanks in advance, Nicole _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha