[Koha-devel] Help with SQL report

[Chris Cormack]

2009/12/1 Adrea Lund <adrea at moablibrary.org>:
> Dear KOHA Users,
>
>
> With the help of Beverly Church at Liblime (Thank You Beverly!!), our
> library is trying to get an SQL report that we can use for weeding our
> collections.  We are getting closer to what we want, but we have gotten
> stuck – are there any SQL gurus out there who can help us?
>
>
>
> Right now we have the following 2 reports… we want to merge them into one
> report (I’ll describe how below).
>
>
>
> Report #1 gives us all items within a specified call number range with less
> than 5 total circulations during a specified time period (circulations
> include issues, renewals and local use).
>
>
>
> select count(*), itemcallnumber as ' call number', dateaccessioned, author,
> title, barcode, datelastseen as 'last seen', itemlost as 'lost status',
> damaged from statistics, items, biblio where statistics.itemnumber =
> items.itemnumber and items.biblionumber = biblio.biblionumber and
> statistics.type in ('issue','renew', 'localuse') and date(datetime) between
> '2004-01-01' and '2009-12-31' and itemcallnumber between 'MUSIC 1999 CD' and
> 'MUSIC ZZ TOP CD' group by statistics.itemnumber having count(*) < 5 order
> by cn_sort
>
>
>
> Report #2 gives us a list breakdown by year of all items within a specified
> call number range that have less than 5 total circulations during any year
> within a specified time frame (circulations include issues, renewals and
> local use).
>
>
>
> select year(datetime) as 'year', count(*), itemcallnumber as ' call number',
> dateaccessioned, author, title, barcode, datelastseen as 'last seen',
> itemlost as 'lost status', damaged from statistics, items, biblio where
> statistics.itemnumber = items.itemnumber and items.biblionumber =
> biblio.biblionumber and statistics.type in ('issue','renew', 'localuse') and
> date(datetime) between '2004-01-01' and '2009-12-31' and itemcallnumber
> between 'MUSIC 1999 CD' and 'MUSIC ZZ TOP CD' group by year,
> statistics.itemnumber having count(*) < 5 order by cn_sort
>
>
>
> We would like to merge them into a report which gives us the “individual”
> circulation counts by year, but also limits the list of materials to ones
> with a total circulation of less than 5 during the total specified time
> period.  Is there a way to include totals and sub-totals on the same
> report?
>
>
>
Hi Andrea

I'm not sure I'm understanding what you are asking for correctly.
Could you put a sample of what you think the output should look like?

Chris