Re: Interesting query outcomes

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