[Koha] Report Help - With Rollup

[Nick Clemens]

It looks like there isn't a direct way to do that, see
here<http://stackoverflow.com/questions/12940119/mysql-change-last-row-of-rollup>,
 but you can wrap the query up and join it to authorised values and make it
work with the caution that any circs without location defined will also end
up labeled "Grand Total' and should be fixed before running the report.

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



On Thu, Mar 20, 2014 at 9:11 AM, Nicole Engard <nengard at gmail.com> wrote:

> Hi all,
>
> I need some help with a report that has With RollUp in it.  I have
> used this before and had no problem, but for this report it keeps
> showing the last value 2 times at the bottom instead of 'grand total'.
>
> So here's how I started:
>
> select IFNULL(a.lib,'*GRAND TOTAL*') as location, count(s.datetime) as
> circs from statistics s left join items i using (itemnumber) left join
> borrowers p using (borrowernumber) LEFT JOIN authorised_values a ON
> (i.location=a.authorised_value) where s.type in ('issue','renew') and
> p.categorycode != 'LIBRARYUSE' and s.branch=<<Check out/renewal
> branch|branches>> and a.category='LOC' 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
>
> This runs and is right, but at the end it repeats the last shelving
> location instead of showing 'Grand Total'.  Next I tried:
>
> select if(i.location is null, '*GRAND TOTAL*', a.lib) as location,
> count(s.datetime) as circs
> from statistics s
> left join items i using (itemnumber)
> left join borrowers p using (borrowernumber)
> LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
> where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
> and s.branch='BAILEYCOVE'
>     and a.category='LOC' and s.datetime between '2014-03-19'
>     and '2014-03-20'
> group by i.location with rollup
>
>
> This repeated the same location over and over.  So then I tried this:
>
> select ifnull(i.location, 'GRAND TOTAL') as code, a.lib as location,
> count(s.datetime) as circs
> from statistics s
> left join items i using (itemnumber)
> left join borrowers p using (borrowernumber)
> LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
> where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
> and s.branch=<<Check out/renewal branch|branches>>
>     and a.category='LOC' 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
>
> Which shows grand total, but still repeats the darn location name again.
>
> And this which shows only one location over and over:
>
> select case a.location when null 'grand total' else a.lib as location,
> count(s.datetime) as circs
> from statistics s
> left join items i using (itemnumber)
> left join borrowers p using (borrowernumber)
> LEFT JOIN authorised_values a ON (i.location=a.authorised_value)
> where s.type in ('issue','renew') and p.categorycode != 'LIBRARYUSE'
> and s.branch=<<Check out/renewal branch|branches>>
>     and a.category='LOC' 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
>
>
> I'm up for any suggestion that gets me to have the final line just
> have the total and not a false location name.
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>



-- 
Nick Clemens
Quechee & Wilder Libraries
Nick at quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341