At 07:18 PM 12/11/2014 -0500, Matthew Charlesworth, S.J. wrote: [snip]

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...

No, the logic breaks down counting itemnumbers -- count the items.biblionumbers instead: 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.biblionumber) 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; Best -- Paul

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