Group By problem...

[The_Chancer]

I am trying to get a list of all entries in a database structure as below:

Company Address Phone Fax Email

Some companies have multiple entries with the same company name but different contact details.

Using the query

SELECT company FROM company  GROUP BY company HAVING count(*) > 1 ORDER BY company ASC

Nicely pulls out all those that have more than one entry. However I also want those in the database with only one entry. Is it possible to have this in the same query ?

Cheers,

[raymie]

unless I am misunderstanding what you want simple change the having clause

SELECT company FROM company GROUP BY company ORDER BY company ASC

or somthing similar should work depending on what database you are using.

You may also find that the order clause is superflous since to group items a sort is needed and so again depending on the database you should find that the rows are returned in the order imposed by the grouping (hence no need in your case for the order clause).

[The_Chancer]

How stupid am I ??

Looking at it again in English and reading it again instead of having code clouded eyes, it is so obvious. In the first case I am trying to select only those "HAVING count(*)>1" not all of them and grouping them by company name...

I think I need a cuppa..