Cindy,<br><br><br>You may also want to try changing<br><br>> WHERE items.ccode = 'BK%'<br><br>...to...<br><br>WHERE items.ccode LIKE 'BK%'<br><br>...if you're trying to match on any ccode starting with "BK"<br>
<br><br>Also, I'm not sure if your ILS has this or not, but in Koha 3.2 and above there is a neat feature in Reports called Runtime Parameters. You can add a place-holder for things like item type or collection code, and rather than specifying them directly in the report, you'll be prompted to add the limitation (or pick from a dropdown) when you run the report. This can greatly consolidate the number of reports you need to save, and provide consistency in output (though, with Koha 3.4 and greater, you have filters on the reports list so you can find which ones you need without scrolling through the complete list).<br>
<br>Cheers,<br><br><br>-Ian<br><br><div class="gmail_quote">On Thu, Jun 30, 2011 at 11:01 PM, Chris Cormack <span dir="ltr"><<a href="mailto:chris@bigballofwax.co.nz">chris@bigballofwax.co.nz</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div><div></div><div class="h5">On 29 June 2011 10:24, Cindy Weber <<a href="mailto:cweber@scls.lib.wi.us">cweber@scls.lib.wi.us</a>> wrote:<br>
> I'm trying to write a report that will list items with the number of<br>
> holds/items over a certain number and then list the number of holds<br>
> placed per library. Since the holds to items ratio is different<br>
> depending on the format of the item (book, DVD, CD, etc.) I'm thinking<br>
> I've got to have one report for each item type.<br>
><br>
> My initial attempt:<br>
> SELECT biblio.biblionumber, reserves.branchcode AS 'Library',<br>
> biblio.title AS 'Title', COUNT(items.biblionumber) AS 'Copies',<br>
> COUNT(reserves.biblionumber) AS 'Holds'<br>
> FROM biblio<br>
> LEFT JOIN items ON (items.biblionumber = biblio.biblionumber)<br>
> LEFT JOIN reserves ON (reserves.biblionumber = items.biblionumber)<br>
> LEFT JOIN biblioitems ON (biblioitems.biblionumber = biblio.biblionumber)<br>
> WHERE items.ccode = 'BK%'<br>
> GROUP BY biblio.biblionumber HAVING<br>
> (COUNT(reserves.biblionumber)/COUNT(items.biblionumber)) > 15<br>
><br>
><br>
> When trying to troubleshoot why this didn't return any results, I<br>
> discovered that<br>
> SELECT biblio.title, COUNT(items.biblionumber), COUNT(reserves.biblionumber)<br>
> FROM biblio<br>
> JOIN items ON (items.biblionumber = biblio.biblionumber)<br>
> JOIN reserves ON (reserves.biblionumber = biblio.biblionumber)<br>
><br>
> returned really large numbers and the items and holds numbers wer all<br>
> equal. For a specific bibionumber, it gave me 520 for both the count of<br>
> items and the count of reserves. There are 8 holds and 65 items for<br>
> that biblionumber. So it's multiplying the two counts together and<br>
> returning that as the result. Changing the JOIN types didn't help any.<br>
> Can anyone please tell me what I'm doing wrong and how to fix this?<br>
><br>
</div></div>Hi Cindy<br>
<br>
We can try, but since you aren't running an official release of Koha,<br>
we can only guess at your database structure. What you probably want<br>
to do, is group the results by biblionumber. You are trying to find<br>
out the number of reserves, and the number of items for each<br>
biblionumber right?<br>
<font color="#888888"><br>
Chris<br>
</font><div><div></div><div class="h5">_______________________________________________<br>
Koha mailing list <a href="http://koha-community.org" target="_blank">http://koha-community.org</a><br>
<a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Ian Walls<br>Lead Development Specialist<br>ByWater Solutions<br>ALA Booth 732<br>Phone # (888) 900-8944<br><a href="http://bywatersolutions.com" target="_blank">http://bywatersolutions.com</a><br>
<a href="mailto:ian.walls@bywatersolutions.com" target="_blank">ian.walls@bywatersolutions.com</a><br>Twitter: @sekjal<br>