Hi all, I'm looking for a report to give the average age of the collection in a few item types. This report runs but isn't right:
SELECT i.itype, avg(b.pubdate REGEXP '^[0-9]+$') as average FROM items i left join (select biblionumber, ExtractValue(marcxml, '//datafield[@tag="264"]/subfield[@code="c"]') as pubdate from biblioitems where ExtractValue(marcxml, '//datafield[@tag="264"]/subfield[@code="c"]') is not null and ExtractValue(marcxml, '//datafield[@tag="264"]/subfield[@code="c"]') != '' union all select biblionumber, copyrightdate as pubdate from biblio where copyrightdate is not null) b using (biblionumber) where i.itype in ('JBK','JPLUS') group by i.itype Also on large collections it takes too long to run so any more efficient suggestions are welcome - I am terrible at math :) Nicole _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha