Nicole, I'm not sure I'm completely understanding what you want to do, but, if I am, does this query work: 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); 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. Regards, Jared On Thu, Dec 2, 2010 at 10:16 AM, Nicole Engard <nengard@gmail.com> wrote:
I am looking at this report in the report library:
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) = 2010 GROUP BY DATE(datetime), substring(itemcallnumber,1,1)
And I'm wondering if anyone knows how I could change that substring bit to show me everything before the first space. For example I want F but I also want JFIC - basically I want to lump it together using the first bit of the call number - the bit before the space.
Thanks in advance, Nicole _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
-- Jared Camins-Esakov Freelance bibliographer, C & P Bibliography Services, LLC (phone) +1 (917) 727-3445 (e-mail) jcamins@cpbibliography.com (web) http://www.cpbibliography.com/