[Koha] Mysql query

[Bernardo Gonzalez Kriegel]

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
bgkriegel at gmail.com


On Thu, Apr 18, 2013 at 12:30 PM, Paul <paul.a at 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 at lists.katipo.co.nz
> http://lists.katipo.co.nz/**mailman/listinfo/koha<http://lists.katipo.co.nz/mailman/listinfo/koha>
>