[KOHA] SQL CIRCULATION REPORT

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[KOHA] SQL CIRCULATION REPORT

muirunyeri
Dear All,

My library uses DDC scheme of classification and we are looking for a
report that will count items checked out allowing us to select day(s) of
issue, select itemtype and group the statistics by DDC 10 broad classes.

*EXAMPLE*

IN a date range say 1st-31st March 2020.

*DDC Class  | NO. of books issued*
000           -               12
100           -               26
200           -               157
.
.
.
900          -               230

The report can generate statistics for a day, month, quarter, e.t.c.

Any assistance however close will be greatly appreciated.

Thank you as you prepare to help

James
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: [KOHA] SQL CIRCULATION REPORT

rogan
Hi James,

One simple way to handle it is with a case statement.  The
following example has a static between filter for the dates but it
illustrates this approach. You can also do some cleaning to make sure there
aren't spaces in front and it assumes that all of your DDC numbers in fact
start with a digit.

SELECT
COUNT(*),
CASE
    WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s'
    WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s'
    WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s'
    WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s'
    WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s'
    WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s'
    WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s'
    WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s'
    WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s'
    WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s'
    ELSE 'OTHER'
    END
from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i
JOIN items it ON it.itemnumber = i.itemnumber
WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY 2 ORDER BY 2;



On Thu, Mar 5, 2020 at 11:06 AM muiru james <[hidden email]> wrote:

> Dear All,
>
> My library uses DDC scheme of classification and we are looking for a
> report that will count items checked out allowing us to select day(s) of
> issue, select itemtype and group the statistics by DDC 10 broad classes.
>
> *EXAMPLE*
>
> IN a date range say 1st-31st March 2020.
>
> *DDC Class  | NO. of books issued*
> 000           -               12
> 100           -               26
> 200           -               157
> .
> .
> .
> 900          -               230
>
> The report can generate statistics for a day, month, quarter, e.t.c.
>
> Any assistance however close will be greatly appreciated.
>
> Thank you as you prepare to help
>
> James
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

[KOHA] SQL CIRCULATION REPORT- how to SUM

muirunyeri
Hi Rogan and Team,

I really appreciate your assistance towards my problem. The query does
indeed work to answer my need. Another query was also pushed to me towards
the same.

I'm now looking for a way to SUM my answer to get the total number of books
issued. Any suggestions would be most welcome for any of the 2 queries. The
other SQL query is as below: -

SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
count(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON
(statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')
  AND date(statistics.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>>
AND <<and (yyyy-mm-dd)|date>>
  AND statistics.itemtype = 'BK'
GROUP BY SUBSTRING(itemcallnumber,1,1)
ORDER BY items.itemcallnumber ASC

N.B My desire is to SUM the NUMBER column as TOTAL.

Please assist team

Warm Regards.
JAMES

On Fri, Mar 6, 2020 at 7:10 PM <[hidden email]> wrote:

> Hi James,
>
> One simple way to handle it is with a case statement.  The
> following example has a static between filter for the dates but it
> illustrates this approach. You can also do some cleaning to make sure there
> aren't spaces in front and it assumes that all of your DDC numbers in fact
> start with a digit.
>
> SELECT
> COUNT(*),
> CASE
>     WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s'
>     WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s'
>     WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s'
>     WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s'
>     WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s'
>     WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s'
>     WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s'
>     WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s'
>     WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s'
>     WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s'
>     ELSE 'OTHER'
>     END
> from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i
> JOIN items it ON it.itemnumber = i.itemnumber
> WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29'
> GROUP BY 2 ORDER BY 2;
>
>
>
> On Thu, Mar 5, 2020 at 11:06 AM muiru james <[hidden email]> wrote:
>
>> Dear All,
>>
>> My library uses DDC scheme of classification and we are looking for a
>> report that will count items checked out allowing us to select day(s) of
>> issue, select itemtype and group the statistics by DDC 10 broad classes.
>>
>> *EXAMPLE*
>>
>> IN a date range say 1st-31st March 2020.
>>
>> *DDC Class  | NO. of books issued*
>> 000           -               12
>> 100           -               26
>> 200           -               157
>> .
>> .
>> .
>> 900          -               230
>>
>> The report can generate statistics for a day, month, quarter, e.t.c.
>>
>> Any assistance however close will be greatly appreciated.
>>
>> Thank you as you prepare to help
>>
>> James
>> _______________________________________________
>>
>> Koha mailing list  http://koha-community.org
>> [hidden email]
>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: [KOHA] SQL CIRCULATION REPORT- how to SUM

rogan
Hi James,

If I'm understanding correctly you want an additional row giving a sum of
all the numbers in addition to the per class one?  The simplest way  it
occurs to me to do that is with a UNION ALL statement.  I've taken some
filters out for brevity but this illustrates the approach:

SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
count(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON
(statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')
GROUP BY SUBSTRING(itemcallnumber,1,1)
UNION ALL
SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON
(statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')

Basically with UNION ALL you can create other queries that don't have to be
tied to the data of the previous queries so long as the columns line up.



On Thu, Mar 12, 2020 at 3:04 AM muiru james <[hidden email]> wrote:

> Hi Rogan and Team,
>
> I really appreciate your assistance towards my problem. The query does
> indeed work to answer my need. Another query was also pushed to me towards
> the same.
>
> I'm now looking for a way to SUM my answer to get the total number of
> books issued. Any suggestions would be most welcome for any of the 2
> queries. The other SQL query is as below: -
>
> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
> count(statistics.type) AS 'NUMBER'
> FROM borrowers
>   LEFT JOIN statistics ON
> (statistics.borrowernumber=borrowers.borrowernumber)
>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE statistics.type IN ('issue', 'renew')
>   AND date(statistics.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>>
> AND <<and (yyyy-mm-dd)|date>>
>   AND statistics.itemtype = 'BK'
> GROUP BY SUBSTRING(itemcallnumber,1,1)
> ORDER BY items.itemcallnumber ASC
>
> N.B My desire is to SUM the NUMBER column as TOTAL.
>
> Please assist team
>
> Warm Regards.
> JAMES
>
> On Fri, Mar 6, 2020 at 7:10 PM <[hidden email]> wrote:
>
>> Hi James,
>>
>> One simple way to handle it is with a case statement.  The
>> following example has a static between filter for the dates but it
>> illustrates this approach. You can also do some cleaning to make sure there
>> aren't spaces in front and it assumes that all of your DDC numbers in fact
>> start with a digit.
>>
>> SELECT
>> COUNT(*),
>> CASE
>>     WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s'
>>     WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s'
>>     WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s'
>>     WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s'
>>     WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s'
>>     WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s'
>>     WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s'
>>     WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s'
>>     WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s'
>>     WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s'
>>     ELSE 'OTHER'
>>     END
>> from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i
>> JOIN items it ON it.itemnumber = i.itemnumber
>> WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29'
>> GROUP BY 2 ORDER BY 2;
>>
>>
>>
>> On Thu, Mar 5, 2020 at 11:06 AM muiru james <[hidden email]> wrote:
>>
>>> Dear All,
>>>
>>> My library uses DDC scheme of classification and we are looking for a
>>> report that will count items checked out allowing us to select day(s) of
>>> issue, select itemtype and group the statistics by DDC 10 broad classes.
>>>
>>> *EXAMPLE*
>>>
>>> IN a date range say 1st-31st March 2020.
>>>
>>> *DDC Class  | NO. of books issued*
>>> 000           -               12
>>> 100           -               26
>>> 200           -               157
>>> .
>>> .
>>> .
>>> 900          -               230
>>>
>>> The report can generate statistics for a day, month, quarter, e.t.c.
>>>
>>> Any assistance however close will be greatly appreciated.
>>>
>>> Thank you as you prepare to help
>>>
>>> James
>>> _______________________________________________
>>>
>>> Koha mailing list  http://koha-community.org
>>> [hidden email]
>>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>>
>>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: [KOHA] SQL CIRCULATION REPORT- how to SUM

rogan
Hi James,

The query I posted worked with my quick test.  Could you post your query as
it is now?



On Sat, Mar 14, 2020 at 9:52 AM muiru james <[hidden email]> wrote:

> Hi Rogan and All,
>
> I cant thank you enough for the tremendous effort you have put towards
> helping me solve my problem.
>
> You do indeed understand well what I need.
>
> The UNION ALL is working well by adding a new row below the last class
> group. However the SUM function is returning a 0 (zero). It seems its not
> adding up the numbers.
>
> Please dont give up but help me find out the last remaining bit. We are
> almost there.
>
> Most appreciated
>
> James
>
>
>
>
>
> On Thu, Mar 12, 2020 at 2:57 PM <[hidden email]> wrote:
>
>> Hi James,
>>
>> If I'm understanding correctly you want an additional row giving a sum of
>> all the numbers in addition to the per class one?  The simplest way  it
>> occurs to me to do that is with a UNION ALL statement.  I've taken some
>> filters out for brevity but this illustrates the approach:
>>
>> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
>> count(statistics.type) AS 'NUMBER'
>> FROM borrowers
>>   LEFT JOIN statistics ON
>> (statistics.borrowernumber=borrowers.borrowernumber)
>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>> items.biblioitemnumber)
>> WHERE statistics.type IN ('issue', 'renew')
>> GROUP BY SUBSTRING(itemcallnumber,1,1)
>> UNION ALL
>> SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
>> FROM borrowers
>>   LEFT JOIN statistics ON
>> (statistics.borrowernumber=borrowers.borrowernumber)
>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>> items.biblioitemnumber)
>> WHERE statistics.type IN ('issue', 'renew')
>>
>> Basically with UNION ALL you can create other queries that don't have to
>> be tied to the data of the previous queries so long as the columns line
>> up.
>>
>>
>>
>> On Thu, Mar 12, 2020 at 3:04 AM muiru james <[hidden email]> wrote:
>>
>>> Hi Rogan and Team,
>>>
>>> I really appreciate your assistance towards my problem. The query does
>>> indeed work to answer my need. Another query was also pushed to me towards
>>> the same.
>>>
>>> I'm now looking for a way to SUM my answer to get the total number of
>>> books issued. Any suggestions would be most welcome for any of the 2
>>> queries. The other SQL query is as below: -
>>>
>>> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
>>> count(statistics.type) AS 'NUMBER'
>>> FROM borrowers
>>>   LEFT JOIN statistics ON
>>> (statistics.borrowernumber=borrowers.borrowernumber)
>>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>>> items.biblioitemnumber)
>>> WHERE statistics.type IN ('issue', 'renew')
>>>   AND date(statistics.datetime) BETWEEN <<Date BETWEEN
>>> (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
>>>   AND statistics.itemtype = 'BK'
>>> GROUP BY SUBSTRING(itemcallnumber,1,1)
>>> ORDER BY items.itemcallnumber ASC
>>>
>>> N.B My desire is to SUM the NUMBER column as TOTAL.
>>>
>>> Please assist team
>>>
>>> Warm Regards.
>>> JAMES
>>>
>>> On Fri, Mar 6, 2020 at 7:10 PM <[hidden email]> wrote:
>>>
>>>> Hi James,
>>>>
>>>> One simple way to handle it is with a case statement.  The
>>>> following example has a static between filter for the dates but it
>>>> illustrates this approach. You can also do some cleaning to make sure there
>>>> aren't spaces in front and it assumes that all of your DDC numbers in fact
>>>> start with a digit.
>>>>
>>>> SELECT
>>>> COUNT(*),
>>>> CASE
>>>>     WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s'
>>>>     WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s'
>>>>     WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s'
>>>>     WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s'
>>>>     WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s'
>>>>     WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s'
>>>>     WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s'
>>>>     WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s'
>>>>     WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s'
>>>>     WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s'
>>>>     ELSE 'OTHER'
>>>>     END
>>>> from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i
>>>> JOIN items it ON it.itemnumber = i.itemnumber
>>>> WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29'
>>>> GROUP BY 2 ORDER BY 2;
>>>>
>>>>
>>>>
>>>> On Thu, Mar 5, 2020 at 11:06 AM muiru james <[hidden email]>
>>>> wrote:
>>>>
>>>>> Dear All,
>>>>>
>>>>> My library uses DDC scheme of classification and we are looking for a
>>>>> report that will count items checked out allowing us to select day(s)
>>>>> of
>>>>> issue, select itemtype and group the statistics by DDC 10 broad
>>>>> classes.
>>>>>
>>>>> *EXAMPLE*
>>>>>
>>>>> IN a date range say 1st-31st March 2020.
>>>>>
>>>>> *DDC Class  | NO. of books issued*
>>>>> 000           -               12
>>>>> 100           -               26
>>>>> 200           -               157
>>>>> .
>>>>> .
>>>>> .
>>>>> 900          -               230
>>>>>
>>>>> The report can generate statistics for a day, month, quarter, e.t.c.
>>>>>
>>>>> Any assistance however close will be greatly appreciated.
>>>>>
>>>>> Thank you as you prepare to help
>>>>>
>>>>> James
>>>>> _______________________________________________
>>>>>
>>>>> Koha mailing list  http://koha-community.org
>>>>> [hidden email]
>>>>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>>>>
>>>>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: [KOHA] SQL CIRCULATION REPORT- how to SUM

muirunyeri
In reply to this post by muirunyeri
Hi Rogan,
I reintroduced two filters date abd itemtype. So that i can sekect any date be it weekly monthly or quarterly etc. I also have different itemtypes sone of which do not start wih a digit and I need to filter them out yo get accurate results. (Your advice on whether the filters might be sn issue is verywelcome)
Below is my query.

SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", count(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')
AND date(datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
AND items.itype = <<Item type|itemtypes>>
GROUP BY SUBSTRING(itemcallnumber,1,1)
UNION ALL
SELECT "TOTAL" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')

Sent from my Samsung Galaxy smartphone.-------- Original message --------From: [hidden email] Date: 15/03/2020  00:48  (GMT+03:00) To: muiru james <[hidden email]>, Koha <[hidden email]> Subject: Re: [KOHA] SQL CIRCULATION REPORT- how to SUM
Hi James,
The query I posted worked with my quick test.  Could you post your query as it is now?


On Sat, Mar 14, 2020 at 9:52 AM muiru james <[hidden email]> wrote:
Hi Rogan and All,

I cant thank you enough for the tremendous effort you have put towards helping me solve my problem.
You do indeed understand well what I need.
The UNION ALL is working well by adding a new row below the last class group. However the SUM function is returning a 0 (zero). It seems its not adding up the numbers.
Please dont give up but help me find out the last remaining bit. We are almost there.
Most appreciated
James





On Thu, Mar 12, 2020 at 2:57 PM <[hidden email]> wrote:
Hi James,
If I'm understanding correctly you want an additional row giving a sum of all the numbers in addition to the per class one?  The simplest way  it occurs to me to do that is with a UNION ALL statement.  I've taken some filters out for brevity but this illustrates the approach:
SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", count(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')
GROUP BY SUBSTRING(itemcallnumber,1,1)
UNION ALL
SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')

Basically with UNION ALL you can create other queries that don't have to be tied to the data of the previous queries so long as the columns line up.  


On Thu, Mar 12, 2020 at 3:04 AM muiru james <[hidden email]> wrote:
Hi Rogan and Team,
I really appreciate your assistance towards my problem. The query does indeed work to answer my need. Another query was also pushed to me towards the same. 
I'm now looking for a way to SUM my answer to get the total number of books issued. Any suggestions would be most welcome for any of the 2 queries. The other SQL query is as below: -
SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS", count(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON (statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber = items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')
  AND date(statistics.datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
  AND statistics.itemtype = 'BK'
GROUP BY SUBSTRING(itemcallnumber,1,1)
ORDER BY items.itemcallnumber ASC

N.B My desire is to SUM the NUMBER column as TOTAL.
Please assist team
Warm Regards. JAMES
On Fri, Mar 6, 2020 at 7:10 PM <[hidden email]> wrote:
Hi James,
One simple way to handle it is with a case statement.  The following example has a static between filter for the dates but it illustrates this approach. You can also do some cleaning to make sure there aren't spaces in front and it assumes that all of your DDC numbers in fact start with a digit.  
SELECT
COUNT(*),
CASE
    WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s'
    WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s'
    WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s'
    WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s'
    WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s'
    WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s'
    WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s'
    WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s'
    WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s'
    WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s'
    ELSE 'OTHER'
    END
from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i
JOIN items it ON it.itemnumber = i.itemnumber
WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY 2 ORDER BY 2;



On Thu, Mar 5, 2020 at 11:06 AM muiru james <[hidden email]> wrote:
Dear All,



My library uses DDC scheme of classification and we are looking for a

report that will count items checked out allowing us to select day(s) of

issue, select itemtype and group the statistics by DDC 10 broad classes.



*EXAMPLE*



IN a date range say 1st-31st March 2020.



*DDC Class  | NO. of books issued*

000           -               12

100           -               26

200           -               157

.

.

.

900          -               230



The report can generate statistics for a day, month, quarter, e.t.c.



Any assistance however close will be greatly appreciated.



Thank you as you prepare to help



James

_______________________________________________



Koha mailing list  http://koha-community.org

[hidden email]

Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha






_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: [KOHA] SQL CIRCULATION REPORT- how to SUM

rogan
Hi James,

A few thoughts from skimming it.

1) I don't see where you're using biblioitems anywhere in the report unless
I'm just not seeing it so you probably don't need it for a join.  I don't
think it'll hurt anything other than slow the report less than  you'll even
notice but still ... if not needed I'd cut it out.

2) you're going to want the same filters in the second query as the first
to make sure you get the same numbers

3) you are using a SUM on the statistics type but you can only sum numbers
you probably want a COUNT there, which you do in the first query but not
the second

On Sun, Mar 15, 2020 at 12:38 AM muirunyeri <[hidden email]> wrote:

> Hi Rogan,
>
> I reintroduced two filters date abd itemtype. So that i can sekect any
> date be it weekly monthly or quarterly etc. I also have different itemtypes
> sone of which do not start wih a digit and I need to filter them out yo get
> accurate results. (Your advice on whether the filters might be sn issue is
> verywelcome)
>
> Below is my query.
>
>
> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
> count(statistics.type) AS 'NUMBER'
> FROM borrowers
>   LEFT JOIN statistics ON
> (statistics.borrowernumber=borrowers.borrowernumber)
>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE statistics.type IN ('issue', 'renew')
> AND date(datetime) BETWEEN <<Date BETWEEN (yyyy-mm-dd)|date>> AND <<and
> (yyyy-mm-dd)|date>>
> AND items.itype = <<Item type|itemtypes>>
> GROUP BY SUBSTRING(itemcallnumber,1,1)
> UNION ALL
> SELECT "TOTAL" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
> FROM borrowers
>   LEFT JOIN statistics ON
> (statistics.borrowernumber=borrowers.borrowernumber)
>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE statistics.type IN ('issue', 'renew')
>
>
> Sent from my Samsung Galaxy smartphone.
> -------- Original message --------
> From: [hidden email]
> Date: 15/03/2020 00:48 (GMT+03:00)
> To: muiru james <[hidden email]>, Koha <[hidden email]>
> Subject: Re: [KOHA] SQL CIRCULATION REPORT- how to SUM
>
> Hi James,
>
> The query I posted worked with my quick test.  Could you post your query
> as it is now?
>
>
>
> On Sat, Mar 14, 2020 at 9:52 AM muiru james <[hidden email]> wrote:
>
>> Hi Rogan and All,
>>
>> I cant thank you enough for the tremendous effort you have put towards
>> helping me solve my problem.
>>
>> You do indeed understand well what I need.
>>
>> The UNION ALL is working well by adding a new row below the last class
>> group. However the SUM function is returning a 0 (zero). It seems its not
>> adding up the numbers.
>>
>> Please dont give up but help me find out the last remaining bit. We are
>> almost there.
>>
>> Most appreciated
>>
>> James
>>
>>
>>
>>
>>
>> On Thu, Mar 12, 2020 at 2:57 PM <[hidden email]> wrote:
>>
>>> Hi James,
>>>
>>> If I'm understanding correctly you want an additional row giving a sum
>>> of all the numbers in addition to the per class one?  The simplest way  it
>>> occurs to me to do that is with a UNION ALL statement.  I've taken some
>>> filters out for brevity but this illustrates the approach:
>>>
>>> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
>>> count(statistics.type) AS 'NUMBER'
>>> FROM borrowers
>>>   LEFT JOIN statistics ON
>>> (statistics.borrowernumber=borrowers.borrowernumber)
>>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>>> items.biblioitemnumber)
>>> WHERE statistics.type IN ('issue', 'renew')
>>> GROUP BY SUBSTRING(itemcallnumber,1,1)
>>> UNION ALL
>>> SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
>>> FROM borrowers
>>>   LEFT JOIN statistics ON
>>> (statistics.borrowernumber=borrowers.borrowernumber)
>>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>>> items.biblioitemnumber)
>>> WHERE statistics.type IN ('issue', 'renew')
>>>
>>> Basically with UNION ALL you can create other queries that don't have to
>>> be tied to the data of the previous queries so long as the columns line
>>> up.
>>>
>>>
>>>
>>> On Thu, Mar 12, 2020 at 3:04 AM muiru james <[hidden email]>
>>> wrote:
>>>
>>>> Hi Rogan and Team,
>>>>
>>>> I really appreciate your assistance towards my problem. The query does
>>>> indeed work to answer my need. Another query was also pushed to me towards
>>>> the same.
>>>>
>>>> I'm now looking for a way to SUM my answer to get the total number of
>>>> books issued. Any suggestions would be most welcome for any of the 2
>>>> queries. The other SQL query is as below: -
>>>>
>>>> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
>>>> count(statistics.type) AS 'NUMBER'
>>>> FROM borrowers
>>>>   LEFT JOIN statistics ON
>>>> (statistics.borrowernumber=borrowers.borrowernumber)
>>>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>>>> items.biblioitemnumber)
>>>> WHERE statistics.type IN ('issue', 'renew')
>>>>   AND date(statistics.datetime) BETWEEN <<Date BETWEEN
>>>> (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>
>>>>   AND statistics.itemtype = 'BK'
>>>> GROUP BY SUBSTRING(itemcallnumber,1,1)
>>>> ORDER BY items.itemcallnumber ASC
>>>>
>>>> N.B My desire is to SUM the NUMBER column as TOTAL.
>>>>
>>>> Please assist team
>>>>
>>>> Warm Regards.
>>>> JAMES
>>>>
>>>> On Fri, Mar 6, 2020 at 7:10 PM <[hidden email]> wrote:
>>>>
>>>>> Hi James,
>>>>>
>>>>> One simple way to handle it is with a case statement.  The
>>>>> following example has a static between filter for the dates but it
>>>>> illustrates this approach. You can also do some cleaning to make sure there
>>>>> aren't spaces in front and it assumes that all of your DDC numbers in fact
>>>>> start with a digit.
>>>>>
>>>>> SELECT
>>>>> COUNT(*),
>>>>> CASE
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s'
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s'
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s'
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s'
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s'
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s'
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s'
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s'
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s'
>>>>>     WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s'
>>>>>     ELSE 'OTHER'
>>>>>     END
>>>>> from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i
>>>>> JOIN items it ON it.itemnumber = i.itemnumber
>>>>> WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29'
>>>>> GROUP BY 2 ORDER BY 2;
>>>>>
>>>>>
>>>>>
>>>>> On Thu, Mar 5, 2020 at 11:06 AM muiru james <[hidden email]>
>>>>> wrote:
>>>>>
>>>>>> Dear All,
>>>>>>
>>>>>> My library uses DDC scheme of classification and we are looking for a
>>>>>> report that will count items checked out allowing us to select day(s)
>>>>>> of
>>>>>> issue, select itemtype and group the statistics by DDC 10 broad
>>>>>> classes.
>>>>>>
>>>>>> *EXAMPLE*
>>>>>>
>>>>>> IN a date range say 1st-31st March 2020.
>>>>>>
>>>>>> *DDC Class  | NO. of books issued*
>>>>>> 000           -               12
>>>>>> 100           -               26
>>>>>> 200           -               157
>>>>>> .
>>>>>> .
>>>>>> .
>>>>>> 900          -               230
>>>>>>
>>>>>> The report can generate statistics for a day, month, quarter, e.t.c.
>>>>>>
>>>>>> Any assistance however close will be greatly appreciated.
>>>>>>
>>>>>> Thank you as you prepare to help
>>>>>>
>>>>>> James
>>>>>> _______________________________________________
>>>>>>
>>>>>> Koha mailing list  http://koha-community.org
>>>>>> [hidden email]
>>>>>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>>>>>
>>>>>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha