Re: [libreoffice-users] LO Base Query - 'count' function behaviour
Yes that works, too. I notice that Base strips off the 'AS' keywords. Cheers Harvey On Tue, 2022-08-16 at 16:02 +0200, Robert Großkopf wrote: > Hi Harvey, > > tested this: > > SELECT COALESCE ( `User`, 'Empty' ) AS `User`, COUNT( `ID` ) FROM > `test`.`User` AS `User` GROUP BY `User` > > You don't need to change the alias for `Location` to `Loc`. > I could save this, open this to edit in GUI, execute it - no Problem > with direct connection on MariaDB. > > When trying the same with MariaDB and JDBC I will get: > Column 'User' in group statement is ambiguous at > /home/buildslave/source/libo- > core/connectivity/source/drivers/jdbc/SQLException.cxx:35 > > Changing the code to > SELECT COALESCE ( `User`, 'Empty' ) AS `Us`, COUNT( `ID` ) FROM > `test`.`User` AS `User` GROUP BY `User` > and the query will work. Won't be detroyed also through GUI. > > GUI will destroy the query when setting > GROUP BY `Us` instead of GROUP BY `User`. Seems it hat problems using > the alias for a group. But the alias isn't needed there, so set > `Location` instead of `Loc` > > Regards > > Robert > -- > Homepage: https://www.familiegrosskopf.de/robert > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LO Base Query - 'count' function behaviour
Hi Harvey, tested this: SELECT COALESCE ( `User`, 'Empty' ) AS `User`, COUNT( `ID` ) FROM `test`.`User` AS `User` GROUP BY `User` You don't need to change the alias for `Location` to `Loc`. I could save this, open this to edit in GUI, execute it - no Problem with direct connection on MariaDB. When trying the same with MariaDB and JDBC I will get: Column 'User' in group statement is ambiguous at /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/SQLException.cxx:35 Changing the code to SELECT COALESCE ( `User`, 'Empty' ) AS `Us`, COUNT( `ID` ) FROM `test`.`User` AS `User` GROUP BY `User` and the query will work. Won't be detroyed also through GUI. GUI will destroy the query when setting GROUP BY `Us` instead of GROUP BY `User`. Seems it hat problems using the alias for a group. But the alias isn't needed there, so set `Location` instead of `Loc` Regards Robert -- Homepage: https://www.familiegrosskopf.de/robert -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LO Base Query - 'count' function behaviour
Just one remark on this, though. The GUI and SQL Edit views seem not to be consistent with each other. Just opening the Query to Edit GUI, adjusting the pane size (say) and saving the result, destroys the query. Cheers Harvey On Tue, 2022-08-16 at 10:54 +0200, Harvey Nimmo wrote: > Thank you, Robert! As always on target. > > This is better than filling the Location fields with some unneeded > value. Great stuff. Man lernt nie aus! > > Cheers > Harvey > > On Tue, 2022-08-16 at 10:15 +0200, Robert Großkopf wrote: > > Hi Harvey, > > > > > > SELECT `Location`, COUNT( `Location` ) FROM > > > `Item_Management`.`Items` > > > `Items` GROUP BY `Location` > > > > > > The result is correct for all LOCATIONS that are not empty, but > > > surpisingly the blank field for the empty locations is > > > accompanied > > > by > > > the value 0. How can I get the right count for the empty fields > > > as > > > well? > > > > Try this: > > SELECT COALESCE(`Location`,'Empty') AS `Loc`, COUNT( `ID` ) FROM > > `Item_Management`.`Items` GROUP BY `Loc` > > > > COUNT: Always use a field which should never be NULL (primary key > > for > > example) > > Set a vallue for a field, which is empta at this moment. If > > "Location" > > contains characters it will work with 'Empty' as shown above. > > > > Didn't test it! > > > > Regards > > > > Robert > > -- > > Homepage: https://www.familiegrosskopf.de/robert > > > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LO Base Query - 'count' function behaviour
Thank you, Robert! As always on target. This is better than filling the Location fields with some unneeded value. Great stuff. Man lernt nie aus! Cheers Harvey On Tue, 2022-08-16 at 10:15 +0200, Robert Großkopf wrote: > Hi Harvey, > > > > SELECT `Location`, COUNT( `Location` ) FROM > > `Item_Management`.`Items` > > `Items` GROUP BY `Location` > > > > The result is correct for all LOCATIONS that are not empty, but > > surpisingly the blank field for the empty locations is accompanied > > by > > the value 0. How can I get the right count for the empty fields as > > well? > > Try this: > SELECT COALESCE(`Location`,'Empty') AS `Loc`, COUNT( `ID` ) FROM > `Item_Management`.`Items` GROUP BY `Loc` > > COUNT: Always use a field which should never be NULL (primary key for > example) > Set a vallue for a field, which is empta at this moment. If > "Location" > contains characters it will work with 'Empty' as shown above. > > Didn't test it! > > Regards > > Robert > -- > Homepage: https://www.familiegrosskopf.de/robert > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] LO Base Query - 'count' function behaviour
Hi Harvey, SELECT `Location`, COUNT( `Location` ) FROM `Item_Management`.`Items` `Items` GROUP BY `Location` The result is correct for all LOCATIONS that are not empty, but surpisingly the blank field for the empty locations is accompanied by the value 0. How can I get the right count for the empty fields as well? Try this: SELECT COALESCE(`Location`,'Empty') AS `Loc`, COUNT( `ID` ) FROM `Item_Management`.`Items` GROUP BY `Loc` COUNT: Always use a field which should never be NULL (primary key for example) Set a vallue for a field, which is empta at this moment. If "Location" contains characters it will work with 'Empty' as shown above. Didn't test it! Regards Robert -- Homepage: https://www.familiegrosskopf.de/robert -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] LO Base Query - 'count' function behaviour
I have a table consisting of (unique) ITEMS and with a column LOCATION for the land of origin of the ITEMS. Some LOCATION fields are still empty. A query counts the number entries in the LOCATION column for each country present. Essentially the GUI just shows the functions LOCATION 'group' and LOCATION 'count'. SELECT `Location`, COUNT( `Location` ) FROM `Item_Management`.`Items` `Items` GROUP BY `Location` The result is correct for all LOCATIONS that are not empty, but surpisingly the blank field for the empty locations is accompanied by the value 0. How can I get the right count for the empty fields as well? Cheers Harvey -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
