[Koha] SQL report for list of authorities with occurrences

[Andreas Roussos]

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