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

[Paul A]

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