At 04:45 PM 8/26/2013 +0530, Madhura Deodhar IES Library wrote:
I want a list of titles having more than one items . Can anyone help me with the SQL query?
Here's one that I wrote for our cataloguers: SELECT CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',items.biblionumber,'\">',biblio.title,'</a>') AS Title, COUNT(items.itemnumber) AS ItemCount FROM items LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) GROUP BY items.biblionumber HAVING (COUNT(*)>1) ORDER BY ItemCount DESC; It gives *clickable* (avoids having to do a 'search' to look at the entry) results like: Title ItemCount Encyclopaedia Britannica - 11th edition / 29 Encyclopaedia Britannica (14th ed.) : 24 The Journal of the Royal Aeronautical Society / 23 Royal Victoria Yacht Club publications / 23 The great events by famous historians : 22 The Canadian Patent Office record : 21 List of shipping / 19 Best - Paul