Dear Andreas, THANK YOU!
It works perfectly for me! All best, Caterina ________________________________ De : Andreas Roussos <arouss1...@gmail.com> Envoyé : mercredi 30 mai 2018 19:18:11 À : Caterina Sansone Cc : koha@lists.katipo.co.nz Objet : Re: [Koha] SQL report for list of authorities with occurrences Dear Caterina, Try pasting the following SQL into a Koha report, it should give you the number of occurrences per authority. Please bear in mind that I made the following assumptions: (a) your Topical Subject authorities go in UNIMARC field 606 of your biblios (b) you have no more than 5 subjects per biblio (in case you do, just add more UNION SELECT statements as required) SELECT t1.authid, ExtractValue( t1.marcxml, '//datafield[@tag=\"250\"]/*' ) AS auth250, COUNT( t2.biblionumber ) AS Count FROM auth_header AS t1, ( SELECT biblionumber, ExtractValue( marcxml, '//datafield[@tag=\"606\"]/subfield[@code=\"9\"][01]' ) AS authid FROM biblioitems UNION SELECT biblionumber, ExtractValue( marcxml, '//datafield[@tag=\"606\"]/subfield[@code=\"9\"][02]' ) AS authid FROM biblioitems UNION SELECT biblionumber, ExtractValue( marcxml, '//datafield[@tag=\"606\"]/subfield[@code=\"9\"][03]' ) AS authid FROM biblioitems UNION SELECT biblionumber, ExtractValue( marcxml, '//datafield[@tag=\"606\"]/subfield[@code=\"9\"][04]' ) AS authid FROM biblioitems UNION SELECT biblionumber, ExtractValue( marcxml, '//datafield[@tag=\"606\"]/subfield[@code=\"9\"][05]' ) AS authid FROM biblioitems ) AS t2 WHERE t1.authid = t2.authid GROUP BY t1.authid ORDER BY auth250 ASC ; I hope this helps! Regards, Andreas _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
