Group By problem

[nero]

Hi,

I have a table with email addresses and when i try to execute the next query

SELECT count(lcase(email)) as cnt,lcase(email) FROM `np_bieter` GROUP BY email having cnt>1

I get wrong results, there are many without email, some with 2 and some with 3 but when I execute the query

in phpAdmin I get also several with 4 as count what is correct.

MySQLyog can't find these or I make a mistake but don't know where.

I tried it with ucase , lcase and whitout case but the result is always the same.

I have tried it with where but no change.

Any idee???

For the rest the program looks o.k. (version 3.02 at the moment)

p.s. MySQL Control Center 0.8.7-alpha has the same problem.

[Medozas]

Actually maybe just giving a hint: But did you already try

Quote:

SELECT count(lcase(email)) as cnt,lcase(email) FROM `np_bieter`where email not null GROUP BY email

WITHOUT HAVING!!

give me create-table script so i can experiment, and ill help… or if it works… great! 😕

[nero]

Hi,

Wel I tried you sugestion but it was not working.

Got error 'You have an error in your SQL syntax near 'null GROUP BY email'

The required result should be when some email was more than 1 time entered.

But your 'email not null' give me a hint to look a bit closer.

Also your request for script was not so bad, but its a mail list and some data inside should then also helpfull.

Some data? 🙁

Well that was the solution. I am working on a server in germany and at home a mirror in the netherlands.

Well synchronizing the tables so my local MySQLyog and MySQL CC have the same data as in germany is a

very good behaviour I think and that was the solution why phpAdmin(in germany after all) worked and the other 2 not.

Stupid me.

But many thanks for your reaction, it did help 🙂

Nero

[Author]

Posts