[Koha] New patrons SQL Report

[muiru james]

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 at 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 at hmcpl.org
>
> On Nov 21, 2020, at 2:54 AM, muiru james <muirunyeri at 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 at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
>
>