[Koha] SQL Report Help

[Chris Cormack]

2010/3/29 Sharon Jackson <sjackson at 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