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

[Liz Rea]

Hi everyone,

Would definitely like to point out the SQL report library at
http://wiki.koha-community.org/wiki/SQL_Reports_Library

It is much better for everyone if you put your contributed reports
there, even if they are duplicate (or slightly duplicate!).

What I would like to suggest, is if you get a report done for you on the
list, that you, the recipient, take the 10 minutes to put that on the
report wiki, crediting your helper, so that everyone can benefit.

It's what community is all about, ya?

Cheers,
Liz
On 12/12/14 13:18, Matthew Charlesworth, S.J. wrote:
> 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
>>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha

-- 
--
Liz Rea
Catalyst.Net Limited
Level 6, Catalyst House, 
150 Willis Street, Wellington.
P.O Box 11053, Manners Street, 
Wellington 6142

GPG: B149 A443 6B01 7386 C2C7 F481 B6c2 A49D 3726 38B7