Hi Paul, Your sql code for unique records by collection code works only if one item type ="book" present by default. I have added one more item type called ="book bank", and added few records to it, and tried to run the query, fetched with duplicate records. When I search, from where these duplicate records came from, it shows, one from book and other from book bank. Therefore, your code works only for one item type ( by collection code). can you please help me how to alter this code, so that , It could fetch only non-repeatable/unique records ( by collection code -or- merging all collections codes) only including item types =book and =book bank.
with thanks. Satish MV Librarian. Govt. Engineering College, Hassan. Karnataka, India. On Thu, Dec 11, 2014 at 12:43 AM, Paul A <[email protected]> wrote: > At 12:09 AM 12/11/2014 +0530, SATISH wrote: > >> [snip] how to achieve for the same tables, >> for each collection ( as per collection code). >> Because, at various time intervals, library inspection team here is most >> interested in >> knowing how many unique titles and counts as a whole library and also by >> collection codes. >> > > 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; > > Paul > > > _______________________________________________ > Koha mailing list http://koha-community.org > [email protected] > http://lists.katipo.co.nz/mailman/listinfo/koha > -- _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
