[Koha] Need help with SQL

[Adrea Lund]

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?  

 

We would greatly appreciate any help!

 

Thank you,

Adrea



Adrea Lund
Head of Adult Services
Grand County Public Library
257 E. Center St.
Moab, UT 84532
435-259-1111 ext11
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20091130/76b69f22/attachment.htm