SQL report for list of authorities with occurrences
Dear all, I have been wandering the web in the last days but couldn't find a solution to my problem : I have a SQL report to obtain a list of authorities (subject : 250a and subfields), but in order to clean the indexes I would like to create a SQL report to obtain a list of authorities with their occurences (how many times each authority has been used). Also, sorting it by alphabetical order would be great. Of course I can see them on Koha but surfing 140 pages is not ideal! We are on Koha 3.22. Does anyone have any idea how to do that? Thanks to all, Caterina
Dear Caterina, When you say "clean the indexes", do you mean finding _unused_ subject authority records and removing them? If the answer is "yes", have a look at the following thread: https://lists.katipo.co.nz/pipermail/koha/2017-November/049404.html On the other hand, if your intention is to correct your authorities (fix misspellings, etc.) based on the number of occurrences for each record a somewhat different approach is required. Let us know what you're trying to achieve and I might be able to help. Kind regards, Andreas On 23 May 2018 at 18:13, Caterina Sansone < caterina.sansone@palais-portedoree.fr> wrote:
Dear all,
I have been wandering the web in the last days but couldn't find a solution to my problem :
I have a SQL report to obtain a list of authorities (subject : 250a and subfields), but in order to clean the indexes I would like to create a SQL report to obtain a list of authorities with their occurences (how many times each authority has been used).
Also, sorting it by alphabetical order would be great.
Of course I can see them on Koha but surfing 140 pages is not ideal!
We are on Koha 3.22.
Does anyone have any idea how to do that?
Thanks to all,
Caterina
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Dear Andreas, Thank you for your answer. By "cleaning the indexes" I mean pruning doubles. For instance, we have a lot of redundant keywords with the same term in singlular or plural (for instance : "immigrant" and "immigrants") that I would like to merge. I should also say that my background is not in IT at all, I am in the library for a short period, and I have to follow Koha among other missions. That means that I am not able to work with php scripts, I was wondering if there is a (relatively) easy way to use a SQL report to do what I wish. I was thinking about a "simple" jointure on two tables but I am unable to find a way to express the relationship between the auth_header table and the biblioitems table. I am not sure if I am making sense... Any ideas? Thanks a lot for your help, Caterina ________________________________ De : Andreas Roussos <arouss1980@gmail.com> Envoyé : mercredi 30 mai 2018 11:35:19 À : Caterina Sansone Cc : koha@lists.katipo.co.nz Objet : Re: [Koha] SQL report for list of authorities with occurrences Dear Caterina, When you say "clean the indexes", do you mean finding _unused_ subject authority records and removing them? If the answer is "yes", have a look at the following thread: https://lists.katipo.co.nz/pipermail/koha/2017-November/049404.html On the other hand, if your intention is to correct your authorities (fix misspellings, etc.) based on the number of occurrences for each record a somewhat different approach is required. Let us know what you're trying to achieve and I might be able to help. Kind regards, Andreas On 23 May 2018 at 18:13, Caterina Sansone <caterina.sansone@palais-portedoree.fr<mailto:caterina.sansone@palais-portedoree.fr>> wrote: Dear all, I have been wandering the web in the last days but couldn't find a solution to my problem : I have a SQL report to obtain a list of authorities (subject : 250a and subfields), but in order to clean the indexes I would like to create a SQL report to obtain a list of authorities with their occurences (how many times each authority has been used). Also, sorting it by alphabetical order would be great. Of course I can see them on Koha but surfing 140 pages is not ideal! We are on Koha 3.22. Does anyone have any idea how to do that? Thanks to all, Caterina _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha
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
Dear Andreas, THANK YOU! It works perfectly for me! All best, Caterina ________________________________ De : Andreas Roussos <arouss1980@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
participants (2)
-
Andreas Roussos -
Caterina Sansone