Hello Martha and All Thank you for your reply and assistance. You solved my problem. Warm Regards James On Mon, Nov 23, 2020 at 5:24 PM Martha Fuerst <mfuerst@hmcpl.org> wrote:
Good morning James -
This should work:
SELECT categorycode, sex, COUNT(borrowernumber) AS 'new patrons' FROM (SELECT borrowernumber, categorycode, dateenrolled, sex FROM borrowers UNION ALL SELECT borrowernumber, categorycode, dateenrolled, sex FROM deletedborrowers) AS patrons WHERE dateenrolled BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> GROUP BY categorycode, sex
Marti Fuerst Systems Librarian Huntsville-Madison County Public Library 915 Monroe St, Huntsville, AL 35801 http://hmcpl.org/ — mfuerst@hmcpl.org
On Nov 21, 2020, at 2:54 AM, muiru james <muirunyeri@gmail.com> wrote:
Hello team,
I need a report that counts and sums new patrons registered within a certain date range by gender for each category.
How can I enhance this report? I dont mind a different on delivering results.
SELECT categorycode, COUNT(borrowernumber) AS 'new patrons' FROM (SELECT borrowernumber, categorycode, dateenrolled FROM borrowers UNION ALL SELECT borrowernumber, categorycode, dateenrolled FROM deletedborrowers) AS patrons WHERE dateenrolled BETWEEN <<Added BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> GROUP BY categorycode
It is not giving me the count by gender. I also want a drop down arrow allowing me to select the category that I want.
Warm Regards
James _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha