[Koha] Report Help - With Rollup

[Nicole Engard]

Okay - now here's the new issue - the grand total does not show:

SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
Circulations FROM (select i.location as loction, count(s.datetime) as
circs from statistics s left join items i using (itemnumber) left join
borrowers p using (borrowernumber) where s.type in ('issue','renew')
and p.categorycode != 'LIBRARYUSE' and s.branch=<<Check out/renewal
branch|branches>> and s.datetime between
<<Checked out or renewed between (yyyy-mm-dd)|date>> and <<and
(yyyy-mm-dd)|date>> group by i.location with rollup ) AS thingy LEFT
JOIN authorised_values a on thingy.loction=a.authorised_value
where a.category='LOC'

On Tue, Mar 25, 2014 at 4:16 PM, Nick Clemens <nick at quecheelibrary.org> wrote:
> Ah, I see now, not a bug, I thought the duplicate authorised_values weren't
> showing up, they were just filing odd because I was using test values and
> not sorting.
>
>
> On Tue, Mar 25, 2014 at 4:03 PM, Nicole Engard <nengard at gmail.com> wrote:
>>
>> Nick,
>>
>> I figured it out - we needed to limit to LOC authorized values:
>>
>> SELECT IFNULL(a.lib,'*GRAND TOTAL*') as Location, thingy.circs AS
>> Circulations FROM (select i.location as loction, count(s.datetime) as
>> circs from statistics s left join items i using (itemnumber) left join
>> borrowers p using (borrowernumber) where s.type in ('issue','renew')
>> and p.categorycode != 'LIBRARYUSE' and s.branch=<<Check out/renewal
>> branch|branches>> and s.datetime between
>> <<Checked out or renewed between (yyyy-mm-dd)|date>> and <<and
>> (yyyy-mm-dd)|date>> group by i.location with rollup ) AS thingy LEFT
>> JOIN authorised_values a on thingy.loction=a.authorised_value
>> where a.category='LOC'
>>
>> On Tue, Mar 25, 2014 at 1:08 PM, Nick Clemens <nick at quecheelibrary.org>
>> wrote:
>> > Here's a thought, it might be an error in the authorized values., and I
>> > think it is a bug
>> >
>> > I recreated it on our system by adding an authorized_value in a
>> > different
>> > category with the same value as one in the LOC:
>> > i.e.
>> > I added AFIC to our Vendor category, with description Error!  The system
>> > doesn't throw an error, and I can't see that value in the listing, but
>> > it is
>> > then 'Adult Fiction' is duplicated in my report
>> >
>> > Is the issue clear?
>> >
>> >
>> >
>> >
>> > On Thu, Mar 20, 2014 at 7:04 PM, Nicole Engard <nengard at gmail.com>
>> > wrote:
>> >>
>> >> Okay, any idea why the Juveasy might be showing 2 times?
>> >>
>> >> http://screencast.com/t/6dbByW4eZ
>> >>
>> >> I added the location code in case that was it - they're identical as
>> >> are their numbers.
>> >>
>> >> On Thu, Mar 20, 2014 at 1:50 PM, Nicole Engard <nengard at gmail.com>
>> >> wrote:
>> >> > Thank you so much Nick!!
>> >> >
>> >> > Nicole
>> >
>> >
>> >
>> >
>> > --
>> > Nick Clemens
>> > Quechee & Wilder Libraries
>> > Nick at quecheelibrary.org
>> > http://www.QuecheeLibrary.org
>> > Q (802) 295-1232 W (802) 295-6341
>
>
>
>
> --
> Nick Clemens
> Quechee & Wilder Libraries
> Nick at quecheelibrary.org
> http://www.QuecheeLibrary.org
> Q (802) 295-1232 W (802) 295-6341