<div>Nicole,</div><div><br></div>I'm not sure I'm completely understanding what you want to do, but, if I am, does this query work:<div><br></div><div><div>SELECT DATE(datetime) AS date, SUBSTRING_INDEX(itemcallnumber, ' ', 1) AS 'Call# range', count(*) AS count FROM statistics LEFT JOIN items USING (itemnumber) WHERE statistics.type IN ('issue', 'renew') AND YEAR(datetime) = 2010 GROUP BY DATE(datetime), SUBSTRING_INDEX(itemcallnumber, ' ', 1);</div>
</div><div><br></div><div>What this should do is select all the text before the first space in a call number. I don't have access to any catalog with circulation data, though, so I can't really test it.</div><div>
<br></div><div>Regards,</div><div>Jared</div><div><br><div class="gmail_quote">On Thu, Dec 2, 2010 at 10:16 AM, Nicole Engard <span dir="ltr"><<a href="mailto:nengard@gmail.com">nengard@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">I am looking at this report in the report library:<br>
<br>
SELECT DATE(datetime) AS date, substring(itemcallnumber,1,1) AS 'Call#<br>
range', count(*) AS count FROM statistics LEFT JOIN items USING<br>
(itemnumber) WHERE statistics.type IN ('issue', 'renew') AND<br>
YEAR(datetime) = 2010 GROUP BY DATE(datetime),<br>
substring(itemcallnumber,1,1)<br>
<br>
And I'm wondering if anyone knows how I could change that substring<br>
bit to show me everything before the first space. For example I want<br>
F but I also want JFIC - basically I want to lump it together using<br>
the first bit of the call number - the bit before the space.<br>
<br>
Thanks in advance,<br>
Nicole<br>
_______________________________________________<br>
Koha mailing list <a href="http://koha-community.org" target="_blank">http://koha-community.org</a><br>
<a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br>
<a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br>
</blockquote></div><br><br clear="all"><br>-- <br>Jared Camins-Esakov<div>Freelance bibliographer, C & P Bibliography Services, LLC</div><div>(phone) +1 (917) 727-3445</div><div>(e-mail) <a href="mailto:jcamins@cpbibliography.com" target="_blank">jcamins@cpbibliography.com</a></div>
<div>(web) <a href="http://www.cpbibliography.com/" target="_blank">http://www.cpbibliography.com/</a></div><br>
</div>