[Koha] [KOHA] SQL CIRCULATION REPORT- how to SUM

[rogan.hamby at gmail.com]

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 <muirunyeri at gmail.com> 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 <rogan.hamby at gmail.com> 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 <muirunyeri at gmail.com> 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 <rogan.hamby at gmail.com> 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 <muirunyeri at gmail.com>
>>>> 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
>>>>> Koha at lists.katipo.co.nz
>>>>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>>>>
>>>>