Re: Interesting query outcomes

On Thursday, August 20, 2020 at 4:28:59 AM UTC-5, Tony Mountifield wrote:
> In article <[email protected]>,
> Joe <[email protected]> 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