[Koha] Report help

[Nicole Engard]

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