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 kbro...@ccpcs.org http://www.ccpcs.org/library/ On Tue, Mar 11, 2014 at 9:28 AM, Nicole Engard <neng...@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 > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha