Hi Nicole, I think you just need to add AND s.type = 'issue' to the WHERE section of your query for x (e.g. right after ba.code = 'JUR'). That got the numbers looking right for my collection. --Katelyn. 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 Tue, Mar 11, 2014 at 9:28 AM, Nicole Engard <nengard@gmail.com> wrote:
I got this request today and I have to admit it's a bit over my head in terms of the math involved. Can someone help me with this report?
-------------
May we have a report that will give us the total circulation for a specific time frame and what percentage was attributed to a specific JUR Code?
I ran this report and for example it gave me 128% as the percentage of total circ for JUR Code M814.
SELECT x.attribute as 'Attribute', x.issues as 'Circ for this attribute', (x.issues * 100)/( SELECT COUNT(s.datetime) as 'total' FROM statistics s WHERE type = 'issue' AND s.datetime BETWEEN <<Date BETWEEN (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>> ) as 'Percentage of total circ' FROM (SELECT ba.attribute as 'attribute', COUNT(s.datetime) as 'issues' FROM statistics s JOIN borrower_attributes ba ON s.borrowernumber = ba.borrowernumber WHERE ba.code = 'JUR' AND s.datetime BETWEEN <<Enter the same dates again BETWEEN (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>> GROUP BY attribute) x _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha