Report Help - With Rollup
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.
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@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
-- Nick Clemens Quechee & Wilder Libraries Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
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@gmail.com> wrote:
Thank you so much Nick!!
Nicole
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@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@gmail.com> wrote:
Thank you so much Nick!!
Nicole
-- Nick Clemens Quechee & Wilder Libraries Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
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@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@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@gmail.com> wrote:
Thank you so much Nick!!
Nicole
-- Nick Clemens Quechee & Wilder Libraries Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
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@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@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@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@gmail.com>
wrote:
Thank you so much Nick!!
Nicole
-- Nick Clemens Quechee & Wilder Libraries Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
-- Nick Clemens Quechee & Wilder Libraries Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
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@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@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@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@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@gmail.com> wrote:
Thank you so much Nick!!
Nicole
-- Nick Clemens Quechee & Wilder Libraries Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
-- Nick Clemens Quechee & Wilder Libraries Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
participants (2)
-
Nick Clemens -
Nicole Engard