[Koha] Correct SQL for getting count of active borrowers

[Jesse]

2009/11/10 Lola McKee <lmckee at 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 at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20091110/8d117e60/attachment.htm 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/gif
Size: 2556 bytes
Desc: not available
Url : http://lists.katipo.co.nz/pipermail/koha/attachments/20091110/8d117e60/attachment.gif