SQL statement returning null rather than empty

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.

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.

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