select with "group by" and "having"

Creating tables and queries

select with "group by" and "having"

Postby Brot » Wed Dec 26, 2007 1:05 pm


I am a oracle database developer. Today I first tried "OpenOffice Base". I have a ods-spreadsheet with a list of persons (first name, last name , address, telephon number, email,...). I created a Base Database with my spreadsheet as existing database. Then I opened the SQL-Editor and tried following statement:
Code: Select all   Expand viewCollapse view
select email, count(*) as quantity
from person
group by email
having count(*) > 1

But this doesn't work. The second column "quantity" is always empty. I would like to find all duplicate email. With "Oracle-SQL" I would write the query as I show before. Is there a possibility to find out all duplicate email in "OpenOffice Base"

For me it seems that "group by" doesn't work in "OpenOffice Base". The query
Code: Select all   Expand viewCollapse view
select last_name
from person
group by last_name

have the same result as
Code: Select all   Expand viewCollapse view
select last_name
from person

I tried all these queries with Open Office Base 2.2 and 2.3

Posts: 1
Joined: Wed Dec 26, 2007 12:49 pm

Re: select with "group by" and "having"

Postby TerryE » Tue Jan 01, 2008 5:44 am

I think that you will find that the issue is due to limitations imposed when you create a database by linking to an existing spreadsheet rather than importing the data into Base. The database engine support for linked spreadsheets is rather primitive. If you open the database in Calc and select its contents (including the header row), you can create a new blank database in Base and paste this selection in as a table. You will find that your standard query syntax works on a native table in base.

Alternatively if you want to stay with the spreadsheet paradigm then you can do the same with a data pilot and a filter of the pilot output table.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests