Re: [Koha] Catalogue - SQL Report Help
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
Thank you both Heather and Paul, for helping me on sql report, This will help many librarians here. On Thu, Dec 11, 2014 at 12:43 AM, Paul A <paul.a@navalmarinearchive.com> 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 Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
--
Hello, Previously I received sql help from Heather Braum, regarding generating unique records, that was a big help. Now, I am looking for generating sql report, that is. Background: I am using 2 kind of item types - (1) book and (2) book bank. ( both are used to catalogue only books) "Book"- is used for all library users where as "book bank"- is used for some reserved category of students and respective circulation rules are made. Requirement: Previously provided sql query (by Heather Braum) fetches unique records (non-repeatable) of only one item type. ( that is =a ) But now I wanted to have a report which has to merge item type 1 (book) and item type 2 (book bank) ( that is = a+b) and then look for unique records from this list. Can you please help me how to achieve this for generating a report. Thank You in advance. Satish MV Librarian Govt. Engineering College, Hassan. Karnataka. INDIA. Previous sql: (1) SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement, t.publishercode, t.isbn, count(i.itemnumber) FROM biblio b LEFT JOIN biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber) GROUP BY b.biblionumber (2)
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 Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
--
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 <paul.a@navalmarinearchive.com> 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 Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
--
Hello, On Sat, Jan 24, 2015 at 1:17 PM, SATISH <lis4satish@gmail.com> wrote:
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.
You have answered your own question :) You simply need to insert an AND to WHERE clause. Google up basic SQL syntax that should get you going. -idg -- Indranil Das Gupta Phone : +91-98300-20971 Blog : http://indradg.randomink.org/blog IRC : indradg on irc://irc.freenode.net Twitter : indradg -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=- Please exchange editable Office documents only in ODF Format. No other format is acceptable. Support Open Standards. For a free editor supporting ODF, please visit LibreOffice - http://www.documentfoundation.org
Hello Good Morning. We maintain two item types for books as Books (BK) and Book Bank (BB) just to accommodate circulation rules/policies. Reason, regular students gets only 2 books from regular stock and reserved students get 2 additional books from Book Bank along with regular stock (BK). And am looking for help for generating unique records (biblio) after merging both BK and BB. I am trying with following sql, but throwing errors. Can you please help me over here. ---------------------------------------------------------------------------------------------------------------------------- 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 items.itype='BB' AND items.itype='BK' GROUP BY b.biblionumber --------------------------------------------------------------------- Error: The database returned the following error: Unknown column 'items.itype' in 'where clause' I am not sure how to proceed further. With Thanks. Satish MV Librarian Govt. Engineering College, Hassan. Karnataka. India. On Sun, Jan 25, 2015 at 2:14 PM, Indranil Das Gupta <indradg@gmail.com> wrote:
Hello,
On Sat, Jan 24, 2015 at 1:17 PM, SATISH <lis4satish@gmail.com> wrote:
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.
You have answered your own question :) You simply need to insert an AND to WHERE clause. Google up basic SQL syntax that should get you going.
-idg
-- Indranil Das Gupta
Phone : +91-98300-20971 Blog : http://indradg.randomink.org/blog IRC : indradg on irc://irc.freenode.net Twitter : indradg
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=- Please exchange editable Office documents only in ODF Format. No other format is acceptable. Support Open Standards.
For a free editor supporting ODF, please visit LibreOffice - http://www.documentfoundation.org
--
participants (3)
-
Indranil Das Gupta -
Paul A -
SATISH