Dear Paul, Heather and Kerrie, Thank you for the report. I've also tweaked it slightly to allow direct interaction with the records in the Staff Interface (using example of CONCAT with A HREF and TITLE codes). (NB Distinguish between ' and "). I attach it here in case you might find it useful. I'm stuck with trying to get a version of the report that only shows 0 copies or copies greater than 1 (or to use the input technique to specify by x number of Copies)... I've tried the WHERE Copies=0 clause but that doesn't seem to work... SELECT DISTINCT CONCAT('<a title="Search for Title with Biblionumber: ',b.biblionumber,'" href="/cgi-bin/koha/catalogue/search.pl?q=ti%3A',REPLACE (b.title, ' ', '+'),'">Search</a>') AS "Search for Title", b.biblionumber, CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'">',b.title,'</a>') AS "Item Title", b.author, t.editionstatement, t.publishercode, t.isbn, count(i.itemnumber) AS "Copies" FROM biblio b LEFT JOIN biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber) GROUP BY b.biblionumber ORDER BY Copies ASC Regards Matthew. ------------------------------
Message: 4 Date: Thu, 11 Dec 2014 01:19:06 +0000 From: Kerrie Stevens <KStevens@harvest.edu.au> Subject: Re: [Koha] Catalogue - SQL Report Help (Paul A)
SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement, t.publishercode, t.isbn, i.ccode, count(i.itemnumber) FROM biblio b LEFT JOIN biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber) WHERE i.ccode=<<Enter collection code>> GROUP BY b.biblionumber
Thanks so much for your sharing of this report.
Kerrie Stevens