CODEWITHSUNDEEP
mysql
RGriffiths
RGriffiths

Reputation: 5970

SQL statement returning null rather than empty

I have an SQL query which is:

SELECT Player, 
Avg(Case When Score1> 0 then Score1 end) AS AverageScore1, 
Avg(Case When Score2> 0 then Score2 end) AS AverageScore2
FROM scores 
WHERE .... various criteria ...

The problem is that this will result set of a single, null record when the criteria is set so that no scores/players should be included.

Is there a way of avoiding getting a null record and the result be empty instead like this example?

enter image description here

Upvotes: 2

Views: 81

Answers (3)

Seamus
Seamus

Reputation: 4819

So you have the table setup similar to this:

create table scores(
  Player varchar(20) not null,
  score1 int not null,
  score2 int not null
);

And your data similar to this:

insert into scores(player, score1, score2) values('player 1', 0, 0);
insert into scores(player, score1, score2) values('player 1', 10, 20);
insert into scores(player, score1, score2) values('player 1', 20, 30);

And when you run the query with criteria similar to this:

SELECT Player, 
    Avg(Case When Score1> 0 then Score1 end) AS AverageScore1, 
    Avg(Case When Score2> 0 then Score2 end) AS AverageScore2
FROM scores
where Score1<10 and Score2<10
GROUP BY Player

You get output like this:

Player     AverageScore1    AverageScore2
---------  ---------------  -----------------
Player1    NULL             NULL

But what you want is:

Player     AverageScore1    AverageScore2
---------  ---------------  -----------------

Is that right?

If so, adding a "HAVING" clause will filter out the records with NULLS:

SELECT Player, 
    Avg(Case When Score1> 0 then Score1 end) AS AverageScore1, 
    Avg(Case When Score2> 0 then Score2 end) AS AverageScore2
FROM scores
where Score1<10 and Score2<10
GROUP BY Player
having Avg(Case When Score1> 0 then Score1 end) is not null and
    Avg(Case When Score2> 0 then Score2 end) is not null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

There is no such thing as "empty" when you are dealing with numeric values. The closest you can come is NULL, which is what your expression returns.

If you want a NULL value, then you should convert the value explicitly to a string. This is important for formatting reasons. You don't specify the database you are using; most databases have a way of formatting a number.

The idea is something like:

SELECT Player, 
       COALESCE(CAST(Avg(Case When Score1 > 0 then Score1 end) as VARCHAR(255)), '') AS AverageScore1, 
       COALESCE(CAST(Avg(Case When Score2 > 0 then Score2 end) as VARCHAR(255)), '') AS AverageScore2
FROM scores 
WHERE .... various criteria ...
GROUP BY Player;

The cast() is just a placeholder for your preferred method of outputting a string, if you decide to go down this route.

Upvotes: 0

Mikel Rychliski
Mikel Rychliski

Reputation: 3597

You can wrap the ISNULL function around the column.

ISNULL(Avg(Case When Score1> 0 then Score1 end),'')

If the first parameter returns a NULL value, it will be replaced by the second parameter.

Upvotes: 1

Related Questions