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

[rogan.hamby at gmail.com]

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
>>>
>>