[Koha] Additonal help needed on ciruclation report

[Joe Atzberger]

>
> between '2009-07-09' and '2009-07-09'
>

It doesn't make much sense to use BETWEEN with the same value.

But your main problem is that you aren't selecting JOINed rows, you're
selecting ALL the rows from the two tables.  Try starting with the
statistics table, then left-joining on the items table.  That's because you
don't want any rows from items except to extend the data found in
statistics.

Try this:

SELECT
  DATE(datetime) AS date,
  substring(itemcallnumber,1,1) AS 'Call# range',
  count(*) AS count
FROM statistics
LEFT JOIN items USING (itemnumber)
WHERE statistics.type IN ('issue', 'renew')
AND   YEAR(datetime) = 2009
AND  MONTH(datetime) = 7
GROUP BY
  DATE(datetime),
  substring(itemcallnumber,1,1)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20090713/58c1f94a/attachment.htm