[Koha] SQL for items with a lot of holds

[Cindy Weber]

I'm trying to write a report that will list items with the number of 
holds/items over a certain number and then list the number of holds 
placed per library.  Since the holds to items ratio is different 
depending on the format of the item (book, DVD, CD, etc.) I'm thinking 
I've got to have one report for each item type. 

My initial attempt:
SELECT biblio.biblionumber, reserves.branchcode AS 'Library', 
biblio.title AS 'Title', COUNT(items.biblionumber) AS 'Copies', 
COUNT(reserves.biblionumber) AS 'Holds'
FROM biblio
LEFT JOIN items ON (items.biblionumber = biblio.biblionumber)
LEFT JOIN reserves ON (reserves.biblionumber = items.biblionumber)
LEFT JOIN biblioitems ON (biblioitems.biblionumber = biblio.biblionumber)
WHERE items.ccode = 'BK%'
GROUP BY biblio.biblionumber HAVING 
(COUNT(reserves.biblionumber)/COUNT(items.biblionumber)) > 15


When trying to troubleshoot why this didn't return any results, I 
discovered that
SELECT biblio.title, COUNT(items.biblionumber), COUNT(reserves.biblionumber)
FROM biblio
JOIN items ON (items.biblionumber = biblio.biblionumber)
JOIN reserves ON (reserves.biblionumber = biblio.biblionumber)

returned really large numbers and the items and holds numbers wer all 
equal.  For a specific bibionumber, it gave me 520 for both the count of 
items and the count of reserves.  There are 8 holds and 65 items for 
that biblionumber.  So it's multiplying the two counts together and 
returning that as the result.  Changing the JOIN types didn't help any.  
Can anyone please tell me what I'm doing wrong and how to fix this? 

Thanks!
Cindy

-- 
Cindy Weber                       cweber at scls.lib.wi.us
System Support Specialist         (608)242-4710
South Central Library System