I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise versa?

Joe

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

I am unable to duplicate this effect.

What is the full structure of your table, and what version of MySQL are you using?


Joe <zhilianghu@gmail.com> wrote in news:6266a024-14d7-43a9-b1bd-0730725130dan@googlegroups.com:

I have a MySQL table field sometimes with NULL or empty values when
there is no data. When I wrote the following to count data in this
field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the
other from "TNAM=''", and also thought they should have been combined
with "group" function... Thoughts without actually update table to
turn "empty" to NULL or vise versa?

Joe

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, August 18, 2020 at 5:34:12 PM UTC-5, Lyle H. Gray wrote:

I am unable to duplicate this effect.

What is the full structure of your table, and what version of MySQL are you using?

UNIX> mysql --version
Ver 14.14 Distrib 5.6.45, for Linux (x86_64) using EditLine wrapper

MYSQL> desc jcrosstbl;
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-----------------------------+
| gn_ID | varchar(12) | NO | PRI | | |
| TNAM | varchar(96) | YES | | NULL | |
| Descrptn | varchar(512) | YES | | NULL | |
| created | datetime | YES | | NULL | |
| updated | datetime | YES | | NULL | on update CURRENT_TIMESTAMP |
| status | int(1) | YES | | NULL |

It also duplicates when I port the table to an older server where MySQL is
Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1.

If I do
mysql> update jcrosstbl set TNAM=NULL where TNAM='';
the duplicates get combined.

joe

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

"Lyle H. Gray" <lylefitzw@no.spam.gmail.com.invalid> wrote in news:XnsAC1E8D2852FE4graynoibisspamcsumas@216.166.97.131:

Have you tried using either the IFNULL() or COALESCE() functions
instead of the CASE WHEN block?

Of course, that would only work if chose to convert a NULL to a blank to
match the other blanks, rather than 'default', so that might not be an
option for you...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

I'm using 5.7.28.

I don't know why this is happening on your server, but you can obviously
verify whether or not the counts represent the different source values by simply using

SELECT TNAM, COUNT(1)
FROM jcrosstbl GROUP BY 1 ;

If the counts match, then I would guess that the GROUP BY is working on
the original column values, rather than the results of the CASE WHEN
(which does not make sense to me).

Have you tried using either the IFNULL() or COALESCE() functions instead
of the CASE WHEN block?


Joe <zhilianghu@gmail.com> wrote in news:29c28bb7-2da7-4793-8083-9a341b24b190n@googlegroups.com:

On Tuesday, August 18, 2020 at 5:34:12 PM UTC-5, Lyle H. Gray wrote:

I am unable to duplicate this effect.

What is the full structure of your table, and what version of MySQL
are you using?

UNIX> mysql --version
Ver 14.14 Distrib 5.6.45, for Linux (x86_64) using EditLine wrapper

MYSQL> desc jcrosstbl;
| Field | Type | Null | Key | Default | Extra
| |
+----------------+--------------+------+-----+---------+--------------- --------------+
| gn_ID | varchar(12) | NO | PRI | |
| | TNAM | varchar(96) | YES | | NULL |
| | Descrptn | varchar(512) | YES |
| | NULL | | created |
| datetime | YES | | NULL | |
| updated | datetime | YES | | NULL | on update
| CURRENT_TIMESTAMP | status | int(1) | YES | | NULL
| |

It also duplicates when I port the table to an older server where
MySQL is Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1.

If I do
mysql> update jcrosstbl set TNAM=NULL where TNAM='';
the duplicates get combined.

joe

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

"Lyle H. Gray" <lylefitzw@no.spam.gmail.com.invalid> wrote in news:XnsAC1E8DD9CD6FCgraynoibisspamcsumas@216.166.97.131:

"Lyle H. Gray" <lylefitzw@no.spam.gmail.com.invalid> wrote in news:XnsAC1E8D2852FE4graynoibisspamcsumas@216.166.97.131:

Have you tried using either the IFNULL() or COALESCE() functions
instead of the CASE WHEN block?

Of course, that would only work if chose to convert a NULL to a blank to match the other blanks, rather than 'default', so that might not be an
option for you...

You might also try using IF(expr1,expr2,expr3) to see if it changes your results.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 8/18/2020 4:33 PM, Joe wrote:

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise versa?

Joe

My quest would be the GROUP BY clause is being applied before the CASE
WHEN clause.


--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 18/08/2020 22.33, Joe wrote:

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise versa?

Maybe you should look at what values you have, try a "select distinct
select concat('"',TNAM,'"') from ITEMS", it could show that you have a
TNAM which begins with default which could hold an extra space
(depending on mysql version, the extra space could be treated differently).

--

//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wednesday, August 19, 2020 at 5:23:21 PM UTC-5, J.O. Aho wrote:

On 18/08/2020 22.33, Joe wrote:

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise versa?

Maybe you should look at what values you have, try a "select distinct
select concat('"',TNAM,'"') from ITEMS", it could show that you have a
TNAM which begins with default which could hold an extra space
(depending on mysql version, the extra space could be treated differently).

--

//Aho

I did:
mysql> select distinct (select concat('"',TNAM'"')) from ITEMS order by 1;
and here is output:
+-----------+
| NULL |
| "" |
+-----------+
among other values. So 'group' treats NULL and "nothing" ('') as separate values - perhaps that's where the mystery is.
I already translated them both to "default"; now all I need to do is to combine the two "default"s.

Thanks all!
joe

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 20/08/2020 02.10, Joe wrote:

On Wednesday, August 19, 2020 at 5:23:21 PM UTC-5, J.O. Aho wrote:

On 18/08/2020 22.33, Joe wrote:

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise versa?

Maybe you should look at what values you have, try a "select distinct
select concat('"',TNAM,'"') from ITEMS", it could show that you have a
TNAM which begins with default which could hold an extra space
(depending on mysql version, the extra space could be treated differently).

I did:
mysql> select distinct (select concat('"',TNAM'"')) from ITEMS order by 1; and here is output:
+-----------+
| NULL |
| "" |
+-----------+
among other values. So 'group' treats NULL and "nothing" ('') as separate values - perhaps that's where the mystery is.
I already translated them both to "default"; now all I need to do is to combine the two "default"s.

You can have a outer select with grouping, this should fix your issue

select a.names, sum(a.counts) as amount from (
select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END as names, count(1) as counts
from ITEMS group by names;
) as a group by a.names

I haven't tested this on mysql, seldom I do write SQL for mysql
nowadays, but should at least work on a t-sql engine like sybase.

--

//Aho

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <6266a024-14d7-43a9-b1bd-0730725130dan@googlegroups.com>,
Joe <zhilianghu@gmail.com> wrote:

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should
have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise
versa?

What does "GROUP BY 1" even mean???

When doing a GROUP BY, the field list in SELECT should only contain either columns listed in the GROUP BY, or aggregate functions. This is enforced
by other SQL engines, but not by MySQL, although it is in the SQL standard.

So your query should be:

SELECT CASE WHEN TNAM IS NULL OR TNAM = '' THEN 'default' ELSE TNAM END AS tnam2, COUNT(*)
FROM ITEMS
GROUP BY tnam2;

Or you could say:

SELECT IFNULL(NULLIF(TNAM, ''), 'default') AS tnam2, COUNT(*)
FROM ITEMS
GROUP BY tnam2;

(not tested)

Cheers
Tony
--
Tony Mountifield
Work: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 20.08.2020 11:28, Tony Mountifield wrote:

In article <6266a024-14d7-43a9-b1bd-0730725130dan@googlegroups.com>,
Joe <zhilianghu@gmail.com> wrote:

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should
have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise
versa?

What does "GROUP BY 1" even mean???

It's a MySQL extension. In ORDER BY and GROUP BY you can refer to the n-th field in the SELECT list by saying just n. A clean formulation of the query would have been to give an alias to the calculated first field and then use that alias name in the GROUP BY clause.

But I doubt that this is the reason for the observed behavior. For me this looks like an ordinary bug. This is confirmed by the fact that others cannot reproduce it and that it is only observed in versions from bronze age (5.6)
or even stone age (5.0). 5.6 will become EOL next February.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 20-8-2020 12:47, Axel Schwenke wrote:

On 20.08.2020 11:28, Tony Mountifield wrote:

In article <6266a024-14d7-43a9-b1bd-0730725130dan@googlegroups.com>,
Joe <zhilianghu@gmail.com> wrote:

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should
have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise
versa?

What does "GROUP BY 1" even mean???

It's a MySQL extension. In ORDER BY and GROUP BY you can refer to the n-th field in the SELECT list by saying just n. A clean formulation of the query would have been to give an alias to the calculated first field and then use that alias name in the GROUP BY clause.

But I doubt that this is the reason for the observed behavior. For me this looks like an ordinary bug. This is confirmed by the fact that others cannot reproduce it and that it is only observed in versions from bronze age (5.6) or even stone age (5.0). 5.6 will become EOL next February.

3 options to get the correct result, followed by the 4th way...

mysql> select case when a='a' then "A" else "a" end, count(*) from (select 'a' as a union all select null) a; +---------------------------------------+----------+
| case when a='a' then "A" else "a" end | count(*) | +---------------------------------------+----------+
| A | 2 | +---------------------------------------+----------+
1 row in set (0.00 sec)

mysql> select case when a='a' then "A" else "a" end, count(*) from (select 'a' as a union all select null) a group by case when a='a' then "A" else "a" end; +---------------------------------------+----------+
| case when a='a' then "A" else "a" end | count(*) | +---------------------------------------+----------+
| A | 2 | +---------------------------------------+----------+
1 row in set (0.00 sec)

mysql> select case when a='a' then "A" else "a" end x, count(*) from (select 'a' as a union all select null) a group by x;
+---+----------+
| x | count(*) |
+---+----------+
| A | 2 |
+---+----------+
1 row in set (0.00 sec)

mysql> select case when a='a' then "A" else "a" end, count(*) from (select 'a' as a union all select null) a group by a; +---------------------------------------+----------+
| case when a='a' then "A" else "a" end | count(*) | +---------------------------------------+----------+
| A | 1 |
| a | 1 | +---------------------------------------+----------+
2 rows in set (0.00 sec)

mysql>

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.21 |
+-----------+
1 row in set (0.00 sec)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <rhln3m$rt9$1@dont-email.me>, Luuk <luuk34@gmail.com> wrote:

On 20-8-2020 12:47, Axel Schwenke wrote:

On 20.08.2020 11:28, Tony Mountifield wrote:

In article <6266a024-14d7-43a9-b1bd-0730725130dan@googlegroups.com>,
Joe <zhilianghu@gmail.com> wrote:

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should
have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise
versa?

What does "GROUP BY 1" even mean???

It's a MySQL extension. In ORDER BY and GROUP BY you can refer to the n-th field in the SELECT list by saying just n. A clean formulation of the query would have been to give an alias to the calculated first field and then use that alias name in the GROUP BY clause.

But I doubt that this is the reason for the observed behavior. For me this looks like an ordinary bug. This is confirmed by the fact that others cannot
reproduce it and that it is only observed in versions from bronze age (5.6) or even stone age (5.0). 5.6 will become EOL next February.

3 options to get the correct result, followed by the 4th way...

I gave two simpler options to get the correct result in my posting further up this thread, but Axel snipped those examples and just replied to my initial query about "GROUP BY 1".

Although at the time I said "untested", I subsequently tried them both out and verified they operated correctly. No need for nested selects, unions, etc.

Cheers
Tony
--
Tony Mountifield
Work: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, August 20, 2020 at 4:28:59 AM UTC-5, Tony Mountifield wrote:

In article <6266a024-14d7-43a9...@googlegroups.com>,
Joe <zhili...@gmail.com> wrote:

I have a MySQL table field sometimes with NULL or empty values when there is no data. When I wrote the following to
count data in this field:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

I got:

| default | 2929 |
| default | 139 |
| item A | 347 |
| item B | 831 |
....
-- why "default" repeats? I humble one is from "TNAM is NULL" and the other from "TNAM=''", and also thought they should
have been combined with "group" function... Thoughts without actually update table to turn "empty" to NULL or vise
versa?

What does "GROUP BY 1" even mean???

I did more experiments and found "group by 1" and "group by TNAM" did gave different outcomes in terms of separate or combines "default" counts. Probably I should stop here as Luuk said my MySQLs are too old. Will yet to to see how it works on the
current version.

Or you could say:

SELECT IFNULL(NULLIF(TNAM, ''), 'default') AS tnam2, COUNT(*)
FROM ITEMS
GROUP BY tnam2;

I like this IFNULL(NULLIF(TNAM, '') construct. That's neat!

joe

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 08/18/2020 10:33 PM, Joe wrote:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

What happens if you do:

select CASE WHEN (TNAM is NULL || TNAM=''|| TNAM='default') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 27-8-2020 18:45, Johann Klammer wrote:

On 08/18/2020 10:33 PM, Joe wrote:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

What happens if you do:

select CASE WHEN (TNAM is NULL || TNAM=''|| TNAM='default') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

a warning will be shown.


mysql> show warnings; +---------+------+-------------------------------------------------------------------------------------------------------+
| Level | Code | Message
| +---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead | +---------+------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, August 27, 2020 at 11:45:59 AM UTC-5, Johann Klammer wrote:

On 08/18/2020 10:33 PM, Joe wrote:

select CASE WHEN (TNAM is NULL || TNAM='') THEN 'default'
ELSE TNAM END, count(1)
from ITEMS group by 1;

What happens if you do:

select CASE WHEN (TNAM is NULL || TNAM=''|| TNAM='default') THEN 'default' ELSE TNAM END, count(1)
from ITEMS group by 1;

Interesting thoughts but it didn't help (tried) because there is no 'default' value in the table.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)