| 
Koha
 » 
Koha - Discuss

Correct SQL for getting count of active borrowers

View: New views
2 Messages — Rating Filter:   Alert me  

Correct SQL for getting count of active borrowers

by Lola McKee :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Some parts of this message have been removed. Learn more about Nabble's security policy. 
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

 

facebook_email

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

 



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

Re: Correct SQL for getting count of active borrowers

by Jesse-34 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message



2009/11/10 Lola McKee <lmckee@...>
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

 

facebook_email

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

 


_______________________________________________
Koha mailing list
Koha@...
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


_______________________________________________
Koha mailing list
Koha@...
http://lists.katipo.co.nz/mailman/listinfo/koha
Free embeddable forum powered by Nabble Forum Help