2009/11/10 Lola McKee <lmckee@salpublib.org>

Can someone tell me the correct SQL to get a count of active borrowers?

Do I need to pull from issues or old_issues and what is the differences and or reasoning.

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

Also do we want to use DISTINCT borrowernumber or just borrowernumber?

Thanks,

Lola McKee

Tech Systems Specialist

Salina Public Library

785-825-4624 Ext. 239

[image: facebook_email]<http://www.facebook.com/pages/Salina-KS/Salina-Public-Library/91712193139?ref=s>

P Please consider the environment before printing this e-mail.

_______________________________________________ Koha mailing list Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha

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. 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. -- Jesse Weaver