[Koha] SQL report for list of authorities with occurrences

[Caterina Sansone]

Dear Andreas,

THANK YOU!

It works perfectly for me!

All best,

Caterina

________________________________
De : Andreas Roussos <arouss1980 at gmail.com>
Envoyé : mercredi 30 mai 2018 19:18:11
À : Caterina Sansone
Cc : koha at 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