CODEWITHSUNDEEP
sqlgroup-bystring
Matt
Matt

Reputation: 273

SQL Replace blank result value in GROUP BY results

I have an SQL query that groups based on values in a column called ks2en. In the column are blank results which are grouping nicely, but I would like to replace the blank values with the text 'No KS2' so that when grouped I get 'No KS2' instead of a blank value.

Here is what my query currently results in:

KS2         No Result   A*
            1           0
2a          0           0
3c          1           0
3b          0           0
3a          1           0
4c          0           0
4b          0           0
4a          0           0
5c          0           0
5b          0           0

Here's what I would like it to look like:

KS2         No Result   A*
No KS2      1           0
2a          0           0
3c          1           0
3b          0           0
3a          1           0
4c          0           0
4b          0           0
4a          0           0
5c          0           0
5b          0           0

And here's my current SQL:

select ks2en as 'KS2',
       count(case result when '' then 1 end) as 'No Result',
       count(case result when 'A*' then 1 end) as 'A*'
  from student join subject 
    on subject.upn=student.upn 
 where name='English'
 group by ks2en
 order by
    case when ks2en = 'W' Then 0 Else 1 End,
    left(ks2en, 1),
    right(ks2en, 1) desc

Upvotes: 0

Views: 1172

Answers (2)

sqlint
sqlint

Reputation: 1089

If there are spaces only.

 select case when LEN(RTRIM(Ks2en)) = 0 then 'No KS2' else 'KS2' end

Upvotes: 1

Twelfth
Twelfth

Reputation: 7180

Coalesce assumes null, not blank. just case statement it instead

case when Ks2en = '' then 'No KS2' else ks2en end as 'KS2'

Upvotes: 2

Related Questions