Select fields which are not null from dynamically generated column name with 'END as' statement

Question

Assume I Have an SQL query like this

     SELECT player_name,weight,
     CASE WHEN weight > 250 THEN 'over 250'
          WHEN weight > 200 THEN '201-250'
          WHEN weight > 175 THEN '176-200'
        ELSE '' END AS weight_group
   FROM benn.college_football_players

I want to display the records which are not null(Weight not null) without disturbing the ELSE ' ',because that would cause so many unnecessary errors in the query,is there a workaround for this? I have used

    where weight_group is not null

at the end,but it's throwing an error as 'Invalid Identifier'

Any other work around other than putting this entire select condition in another select statement?

please make it clear whether you want to exclude those rows where `weight_group` column is empty or where `weight` column is null? — Yousaf, Jan 29 '18 at 11:48

score 1 · Accepted Answer · answered Jan 29 '18 at 11:30

NULL is not equal to '' in MySQL. NULL means no data and empty string means empty string, they are two different things.

You need to filter data on the basis of empty string like

where weight_group != '';

Now you can achieve desired result in more than one way.

1. Using Sub-Query

SELECT * 
FROM (SELECT player_name,weight,
             CASE 
                WHEN weight > 250 THEN 'over 250' 
                WHEN weight > 200 THEN '201-250' 
                WHEN weight > 175 THEN '176-200' 
                ELSE '' 
            END AS weight_group 
      FROM benn.college_football_players) AS t 
WHERE weight_group != '';

2. Using Group By

SELECT player_name,weight,
         CASE WHEN weight > 250 THEN 'over 250'
              WHEN weight > 200 THEN '201-250'
              WHEN weight > 175 THEN '176-200'
              ELSE '' 
          END AS weight_group
FROM benn.college_football_players
GROUP BY weight_group, weight, player_name
HAVING weight_group != '';

score 0 · Answer 2 · answered Jan 29 '18 at 11:15

You can add just where condition where weight > 175

SELECT player_name,weight,
 CASE WHEN weight > 250 THEN 'over 250'
      WHEN weight > 200 THEN '201-250'
      WHEN weight > 175 THEN '176-200'
    ELSE '' END AS weight_group FROM benn.college_football_players where weight > 175

Here else part not return any thing. Also you can use below code too

SELECT player_name,weight,
 CASE WHEN weight > 250 THEN 'over 250'
      WHEN weight > 200 THEN '201-250'
      WHEN weight > 175 THEN '176-200'
END AS weight_group FROM benn.college_football_players where weight > 175

I can't assign any values to where condition,i meant i can't use comparison operators — KnowNothing, Jan 29 '18 at 11:25

Select fields which are not null from dynamically generated column name with 'END as' statement

2 Answers2