[Koha] Report Help - With Rollup

[Nicole Engard]

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.