Re: Interesting query outcomes

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???

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: [email protected] - http://www.softins.co.uk
Play: [email protected] - http://tony.mountifield.org