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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha