Items held by more than one branch
Hi, all-- Another report question... Trying to write a report that will provide details for items held by more than one branch, with separate output for each library's item. Including the statement HAVING COUNT(i.itemnumber) > 1 collapses the results to just one title. (Strangely, the bib returned only has a single item attached.) I'm missing some basic feature of how SQL operates :-( SELECT b.biblionumber,i.homebranch,t.isbn,b.title,b.author,i.ccode,i.itype FROM items i LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems t USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 HAVING COUNT(i.itemnumber) > 1 ORDER BY b.title Grateful in advance for any assistance! All best, Cab Vinton Plaistow Public Library Plaistow, NH
I'd say you are missing a group by. On Mon, 23 Apr 2018 at 10:19 Cab Vinton <bibliwho@gmail.com> wrote:
Hi, all--
Another report question...
Trying to write a report that will provide details for items held by more than one branch, with separate output for each library's item.
Including the statement HAVING COUNT(i.itemnumber) > 1 collapses the results to just one title. (Strangely, the bib returned only has a single item attached.)
I'm missing some basic feature of how SQL operates :-(
SELECT b.biblionumber,i.homebranch,t.isbn,b.title,b.author,i.ccode,i.itype FROM items i LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems t USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 HAVING COUNT(i.itemnumber) > 1 ORDER BY b.title
Grateful in advance for any assistance!
All best,
Cab Vinton Plaistow Public Library Plaistow, NH _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Hi, I want to convert this checked in sql report into renew sql report. How i can convert this report. guide me. SELECT b.title, b.author, i.itemcallnumber, i.barcode, c.returndate FROM (SELECT iss.returndate, iss.itemnumber FROM issues iss, (SELECT @StartDate:=<<Between (yyyy-mm-dd)|date>>,@EndDate:=<<and (yyyy-mm-dd)|date>>) AS var WHERE date(iss.returndate) BETWEEN @StartDate AND @EndDate UNION ALL SELECT oi.returndate, oi.itemnumber FROM old_issues oi WHERE date(oi.returndate) BETWEEN @StartDate AND @EndDate) AS c LEFT JOIN items i USING (itemnumber) LEFT JOIN biblio b USING (biblionumber) WHERE i.homebranch=<<Item belongs TO|branches>> On Mon, Apr 23, 2018 at 7:14 PM, Jonathan Druart < jonathan.druart@bugs.koha-community.org> wrote:
I'd say you are missing a group by.
On Mon, 23 Apr 2018 at 10:19 Cab Vinton <bibliwho@gmail.com> wrote:
Hi, all--
Another report question...
Trying to write a report that will provide details for items held by more than one branch, with separate output for each library's item.
Including the statement HAVING COUNT(i.itemnumber) > 1 collapses the results to just one title. (Strangely, the bib returned only has a single item attached.)
I'm missing some basic feature of how SQL operates :-(
SELECT b.biblionumber,i.homebranch,t.isbn,b.title,b.author,i.ccode, i.itype FROM items i LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems t USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 HAVING COUNT(i.itemnumber) > 1 ORDER BY b.title
Grateful in advance for any assistance!
All best,
Cab Vinton Plaistow Public Library Plaistow, NH _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- *Muhammad Arshad Iqbal* Assistant Librarian at Publishing and Student Affairs Directorate, National University of Science and Technology (NUST) Islamabad. Phone number:051-90851371 Cell no.+923444900809
Thank you, Jonathan. That fixes one problem (output now appears to be for the right bib numbers), but the GROUP function introduces another issue -- namely, it collapses the results so that just one item is show for each bib number: 14820 PLAISTOW 1572151714 : '20s & '30s style / Horsham, Michael. NONFICTION BOOK when what I originally was after was: 14820 PLAISTOW 1572151714 : '20s & '30s style / Horsham, Michael. NONFICTION BOOK 14820 KIMBALL 1572151714 : '20s & '30s style / Horsham, Michael. NONFICTION BOOK Looks like I'll have to find a way to include information for all items on that single line. We have 3 branches, so output would vary depending on whether a particular item is owned by just 2 or all 3 libraries. (Btw, I know the above is redundant, but ultimately I'll be adding fields that are not the same -- price, ordering source, etc.) Thanks again, Cab Vinton On Mon, Apr 23, 2018 at 10:14 AM, Jonathan Druart < jonathan.druart@bugs.koha-community.org> wrote:
I'd say you are missing a group by.
On Mon, 23 Apr 2018 at 10:19 Cab Vinton <bibliwho@gmail.com> wrote:
Hi, all--
Another report question...
Trying to write a report that will provide details for items held by more than one branch, with separate output for each library's item.
Including the statement HAVING COUNT(i.itemnumber) > 1 collapses the results to just one title. (Strangely, the bib returned only has a single item attached.)
I'm missing some basic feature of how SQL operates :-(
SELECT b.biblionumber,i.homebranch,t.isbn,b.title,b.author,i.ccode, i.itype FROM items i LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems t USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 HAVING COUNT(i.itemnumber) > 1 ORDER BY b.title
Grateful in advance for any assistance!
All best,
Cab Vinton Plaistow Public Library Plaistow, NH _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
On Mon, Apr 23, 2018 at 09:19:10AM -0400, Cab Vinton wrote:
Trying to write a report that will provide details for items held by more than one branch, with separate output for each library's item.
Put the HAVING clause inside a subquery that determines which bibs' items should be included -- something like this (untested): SELECT b.biblionumber, i.homebranch, t.isbn, b.title, b.author, i.ccode, i.itype FROM items i LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems t USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 AND b.biblionumber IN ( SELECT b.biblionumber FROM items i LEFT JOIN biblio b USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 HAVING COUNT(i.itemnumber) > 1 ) ORDER BY b.title That's a bunch of data for all items (not lost or withdrawn) on bibs that have more than one item (not lost or withdrawn). HTH, Paul. -- Paul Hoffman <paul@flo.org> Software Manager Fenway Library Organization 550 Huntington Ave. Boston, MA 02115 (617) 442-2384 (FLO main number)
Sorry, no dice -- returns a single item :-( The mysteries of SQL ... Cheers, Cab On Mon, Apr 23, 2018 at 5:06 PM, Paul Hoffman <paul@flo.org> wrote:
On Mon, Apr 23, 2018 at 09:19:10AM -0400, Cab Vinton wrote:
Trying to write a report that will provide details for items held by more than one branch, with separate output for each library's item.
Put the HAVING clause inside a subquery that determines which bibs' items should be included -- something like this (untested):
SELECT b.biblionumber, i.homebranch, t.isbn, b.title, b.author, i.ccode, i.itype FROM items i LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems t USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 AND b.biblionumber IN ( SELECT b.biblionumber FROM items i LEFT JOIN biblio b USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 HAVING COUNT(i.itemnumber) > 1 ) ORDER BY b.title
That's a bunch of data for all items (not lost or withdrawn) on bibs that have more than one item (not lost or withdrawn).
HTH,
Paul.
-- Paul Hoffman <paul@flo.org> Software Manager Fenway Library Organization 550 Huntington Ave. Boston, MA 02115 (617) 442-2384 (FLO main number) _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Cab - This is a little (ok..a lot) quick and dirty, but does return results... does this help? SELECT i.biblionumber,b.title, homebranch,itemcallnumber,barcode,itype,location,ccode,itemlost,withdrawn FROM items i LEFT JOIN biblio b USING (biblionumber) where biblionumber in (select biblionumber from items group by biblionumber having count(distinct(homebranch)) >1) order by i.biblionumber On Mon, Apr 23, 2018 at 5:39 PM, Cab Vinton <bibliwho@gmail.com> wrote:
Sorry, no dice -- returns a single item :-(
The mysteries of SQL ...
Cheers,
Cab
On Mon, Apr 23, 2018 at 5:06 PM, Paul Hoffman <paul@flo.org> wrote:
On Mon, Apr 23, 2018 at 09:19:10AM -0400, Cab Vinton wrote:
Trying to write a report that will provide details for items held by more than one branch, with separate output for each library's item.
Put the HAVING clause inside a subquery that determines which bibs' items should be included -- something like this (untested):
SELECT b.biblionumber, i.homebranch, t.isbn, b.title, b.author, i.ccode, i.itype FROM items i LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems t USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 AND b.biblionumber IN ( SELECT b.biblionumber FROM items i LEFT JOIN biblio b USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 HAVING COUNT(i.itemnumber) > 1 ) ORDER BY b.title
That's a bunch of data for all items (not lost or withdrawn) on bibs that have more than one item (not lost or withdrawn).
HTH,
Paul.
-- Paul Hoffman <paul@flo.org> Software Manager Fenway Library Organization 550 Huntington Ave. Boston, MA 02115 (617) 442-2384 (FLO main number) _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- Joy Nelson Vice President of Implementations ByWater Solutions <http://bywatersolutions.com> Support and Consulting for Open Source Software Phone/Fax (888)900-8944 What is Koha? <http://bywatersolutions.com/what-is-koha/>
Thank you, Joy -- I think that did the trick. Nice work! Cab On Mon, Apr 23, 2018 at 7:09 PM, Joy Nelson <joy@bywatersolutions.com> wrote:
Cab - This is a little (ok..a lot) quick and dirty, but does return results... does this help?
SELECT i.biblionumber,b.title, homebranch,itemcallnumber,barcode,itype,location,ccode,itemlost,withdrawn FROM items i LEFT JOIN biblio b USING (biblionumber) where biblionumber in (select biblionumber from items group by biblionumber having count(distinct(homebranch)) >1) order by i.biblionumber
On Mon, Apr 23, 2018 at 5:39 PM, Cab Vinton <bibliwho@gmail.com> wrote:
Sorry, no dice -- returns a single item :-(
The mysteries of SQL ...
Cheers,
Cab
On Mon, Apr 23, 2018 at 5:06 PM, Paul Hoffman <paul@flo.org> wrote:
On Mon, Apr 23, 2018 at 09:19:10AM -0400, Cab Vinton wrote:
Trying to write a report that will provide details for items held by more than one branch, with separate output for each library's item.
Put the HAVING clause inside a subquery that determines which bibs' items should be included -- something like this (untested):
SELECT b.biblionumber, i.homebranch, t.isbn, b.title, b.author, i.ccode, i.itype FROM items i LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems t USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 AND b.biblionumber IN ( SELECT b.biblionumber FROM items i LEFT JOIN biblio b USING (biblionumber) WHERE i.itemlost=0 AND i.withdrawn=0 HAVING COUNT(i.itemnumber) > 1 ) ORDER BY b.title
That's a bunch of data for all items (not lost or withdrawn) on bibs that have more than one item (not lost or withdrawn).
HTH,
Paul.
-- Paul Hoffman <paul@flo.org> Software Manager Fenway Library Organization 550 Huntington Ave. Boston, MA 02115 (617) 442-2384 (FLO main number) _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
-- Joy Nelson Vice President of Implementations
ByWater Solutions Support and Consulting for Open Source Software Phone/Fax (888)900-8944 What is Koha?
participants (5)
-
Arshad Iqbal -
Cab Vinton -
Jonathan Druart -
Joy Nelson -
Paul Hoffman