[Koha] SQL report for duplicate items

Fri Jul 24 08:20:47 NZST 2009

Joy,

You may want to try this query:

select title,count(*) from biblio group by title having count(*) > 1

This would find all the duplicate titles.  You may wish to add 'biblionumber' to the SELECT clause to be able to uniquely point to a record.

This may not be exactly what you're looking for; your original post said duplicate items, and in a FRBRized world, you could have the same title for two different imprints of a Work.  Modifying the SQL slightly, you could try this:

SELECT itemnumber, itemcallnumber, count(itemcallnumber) FROM items GROUP BY itemcallnumber HAVING COUNT(itemcallnumber) > 1;

This would give you a list of offending call numbers, with their attached item number for unique reference.   You could also throw barcode in there, if that's handy.  If you wanted the list to show titles, you'd have to have a more complex query with a join.

Hope this helps,

Ian Walls
Systems Integration Librarian
NYU Health Sciences Libraries
550 First Ave., New York, NY 10016
(212) 263-8687

2009/7/23 Banks, Joy M <jbanks at flsouthern.edu<mailto:jbanks at flsouthern.edu>>:
> Hi everyone-
>
>
>
> We're a hosted Koha library using whatever the most recent release of the
> product is. I've been asked if I can generate a report through Koha that
> would tell us all the duplicate items in the collection. Try as I might, I
> can't seem to figure out a simple way of doing this without pulling a report
> of every single item, sorting by call number, and sifting through the tens
> of thousands of items. Has anyone generated a report like this? It wouldn't
> be enough to pull titles that have more than one record because then we'll
> get multi-volume sets too. And there isn't a single identifying entry in the
> call number (e.g. pt. or c.) because there hasn't been any consistency with
> our tracking of duplicate items. If anyone has ideas, please let me know.
> I'll only be able to copy/paste generated SQL into the Report Wizard. We
> have no access to the backend of the system.
>
>
>
> Thank you!
>
>
>
> ~Joy
>
>
>
> Joy M. Banks
>
> Catalog Librarian
>
> Roux Library
>
> Florida Southern College
>
> 111 Lake Hollingsworth Dr.
>
> Lakeland, FL 33801
>
> (863)680-4736
>
> jbanks at flsouthern.edu<mailto:jbanks at flsouthern.edu>
>
>
>
>
>
> _______________________________________________
> Koha mailing list
> Koha at lists.katipo.co.nz<mailto:Koha at lists.katipo.co.nz>
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
_______________________________________________
Koha mailing list
Koha at lists.katipo.co.nz<mailto:Koha at lists.katipo.co.nz>
http://lists.katipo.co.nz/mailman/listinfo/koha

--
-----------------
Beverly Church
Project Manager - LibLime
phone: 1.888.564.2457 x717
email: beverly.church at liblime.com<mailto:beverly.church at liblime.com>
skype: beverlychurch
</PRE>
<html>
<body>
------------------------------------------------------------<br />
This email message, including any attachments, is for the sole use of the intended recipient(s) and may contain information that is proprietary, confidential, and exempt from disclosure under applicable law. Any unauthorized review, use, disclosure, or distribution is prohibited. If you have received this email in error please notify the sender by return email and delete the original message. Please note, the recipient should check this email and any attachments for the presence of viruses. The organization accepts no liability for any damage caused by any virus transmitted by this email.<br />
=================================
</body>
</html>
<PRE>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20090723/2a0f4024/attachment.htm 