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 <kstev...@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
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha
