[Koha] Catalogue - SQL Report Help (Paul A)

[Matthew Charlesworth, S.J.]

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