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

[muiru james]

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