Why not first select those biblios with item.price >= XX SELECT ...what you need... FROM ( SELECT sum(price) as totalprice, biblionumber FROM items WHERE price >= XX GROUP BY biblionumber ) as biblio_price LEFT JOIN biblioitems on (biblio_price.biblionumber=**biblio items.biblionumber) LEFT JOIN biblio on (biblioitems.biblionumber=**biblio.biblionumber) ... etc
Bernardo -- Bernardo Gonzalez Kriegel bgkrie...@gmail.com On Thu, Apr 18, 2013 at 12:30 PM, Paul <pau...@aandc.org> wrote: > I'm looking, please, for some expertise in writing MySQL queries (Koha > "reports".) I have a good "report" for listing high value items in our > collections (a requirement for our insurers), but need to add the "special > case" of multi volume biblios. > > Example: a set of Britannicas (14th ed.) is a single biblio with 24 > "items" at $25 each for a total of $600, above our declared insurance > threshold (we price items individually to cover incomplete sets.) > > Question: How can I "SUM" all items under a single biblio in a manner that > will allow inclusion on the "HAVING" line below? > > SELECT > items.price,biblio.author,**biblio.title,ExtractValue(**biblioitems.marcxml, > '//datafield[@tag="245"]/**subfield[@code="b"]') AS subtitle FROM items > LEFT JOIN biblioitems on (items.biblioitemnumber=** > biblioitems.biblioitemnumber) > LEFT JOIN biblio on (biblioitems.biblionumber=**biblio.biblionumber) > HAVING items.price >= <<Value more than>> > ORDER BY items.price DESC; > > I *suspect* it's something along the lines of: > > SET @price1=0; > SELECT items.price, (@price1:=SUM(items.price)) as FullValue, @price1 as > returned_val > > but cannot get this to function properly (it returns the value of the > whole catalogue) -- the following: > > SET @price1=0; > SELECT items.price, (@price1:=SUM(items.price)) as FullValue, @price1 as > returned_val, > biblio.author,biblio.title,**ExtractValue(biblioitems.**marcxml, > '//datafield[@tag="245"]/**subfield[@code="b"]') AS subtitle FROM items > LEFT JOIN biblioitems on (items.biblioitemnumber=** > biblioitems.biblioitemnumber) > LEFT JOIN biblio on (biblioitems.biblionumber=**biblio.biblionumber) > HAVING FullValue >= 500 > ORDER BY FullValue DESC; > > does *not* even save in Koha (Error 1 "see logs", but I can't find > anything - suspect it's the ; on line 1.) But on the command line, it runs > without syntactical error, but lists only biblionumber=1 and the total > value of everything we have ever catalogued ;={ > > Maybe it's not possible and a Perl script is required? > > Many thanks for any suggestions. > > Paul > > ______________________________**_________________ > Koha mailing list http://koha-community.org > Koha@lists.katipo.co.nz > http://lists.katipo.co.nz/**mailman/listinfo/koha<http://lists.katipo.co.nz/mailman/listinfo/koha> > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha