Thanks! I'll ask the library if this is what they meant/wanted. Nicole
On Tue, May 20, 2014 at 2:49 PM, Katelyn Browne <kbro...@ccpcs.org> wrote: > 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 > kbro...@ccpcs.org > http://www.ccpcs.org/library/ > > > On Thu, May 15, 2014 at 10:51 PM, Nicole Engard <neng...@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_by_collection_codebut >> 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 >> > > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha