2010/3/29 Sharon Jackson <sjackson@ashs.school.nz>:

Hi,

I'm trying to run a report that gives me a list of the most popular DVDs for the current month.  When I run the report, I get a list of DVDs that all have an issue count of one,(which I know is not the case). And when I look at the checkout history for DVDs that I know are popular -  some don't appear on the list at all, and all have an issue count much higher than one for that time period. Any ideas/help would be much appreciated

SELECT biblio.title,biblio.author,COUNT(*) AS issuecount FROM issues,biblio,items WHERE items.biblionumber = biblio.biblionumber AND issues.itemnumber= items.itemnumber AND issues.issuedate > '2010-02-28' AND items.itype='DVD' GROUP BY biblio.biblionumber ORDER BY issuecount DESC LIMIT 20

Hi Sharon The problem is the issues table only contains those items currently on issue. So joining to that will only ever give you the items currently on issue. The old_issues table contains those that have been returned. What I would do is query the statistics table; eg SELECT biblio.title,biblio.author,count(*) AS issuecount FROM statistics,items,biblio WHERE statistics.type = 'issue' AND biblio.biblionumber=items.biblionumber AND items.itemnumber = statistics.itemnumber AND items.itype='DVD' AND datetime > '2010-02-28' GROUP BY biblio.biblionumber; Give that a whirl and let me know how you get on. Chris