<br><br><div class="gmail_quote">2009/11/10 Lola McKee <span dir="ltr"><<a href="mailto:lmckee@salpublib.org">lmckee@salpublib.org</a>></span><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="purple" lang="EN-US">
<div><pre>Can someone tell me the correct SQL to get a count of active borrowers? </pre><pre> </pre><pre>Do I need to pull from issues or old_issues and what is the differences and or reasoning. </pre><pre> </pre><pre>We used the SQL from Koha SQL Reports Library “List Active Patrons by Category for a Specific Month” Code = SELECT YEAR(issuedate), MONTH(issuedate), categorycode, COUNT(DISTINCT borrowernumber) FROM old_issues LEFT JOIN borrowers USING (borrowernumber) GROUP BY YEAR(issuedate), MONTH(issuedate), categorycode</pre>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Also do we want to use DISTINCT borrowernumber or just
borrowernumber?</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Thanks,</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span style="font-size: 24pt; font-family: "Brush Script MT"; color: rgb(54, 95, 145);">Lola McKee</span></p>
<p class="MsoNormal">Tech Systems Specialist</p>
<p class="MsoNormal">Salina Public Library</p>
<p class="MsoNormal">785-825-4624 Ext. 239</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><a href="http://www.facebook.com/pages/Salina-KS/Salina-Public-Library/91712193139?ref=s" target="_blank"><span style="color: blue; text-decoration: none;"><img src="cid:image001.gif@01CA6207.002B6EE0" alt="facebook_email" width="79" border="0" height="31"></span></a></p>
<p class="MsoNormal"><span style="font-size: 18pt; font-family: Webdings; color: green;">P</span><span style="font-size: 10pt; color: green;"> </span><span style="font-size: 10pt; font-family: "Comic Sans MS"; color: green;">Please consider the environment before printing this e-mail.</span></p>
<p class="MsoNormal"> </p>
</div>
</div>
<br>_______________________________________________<br>
Koha mailing list<br>
<a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
<br></blockquote></div><br>You'd generally want to use old_issues. issues only includes current checkouts, while old_issues only includes returned checkouts. Including everything in one query would require subqueries or UNION SELECTs and the SQL would get a bit hoary; running the same query twice with issues and old_issues and merging by hand would be easier if recent checkouts are important.<br>
<br>COUNT(borrowernumber) will only give you a count of those rows where a borrowernumber is set (all of them). You need to use COUNT(DISTINCT borrowernumber) to give a count of how many different borrowers checked out items within a given month.<br clear="all">
<br>-- <br>Jesse Weaver<br>