CODEWITHSUNDEEP
sqlsql-server
dotNetkow
dotNetkow

Reputation: 5313

SQL: Combine value into one result when NULL or empty

I have a SQL query that returns two columns - "Title" and "Count". When "Title" is NULL or empty (''), I want to combine the result into one row. How can I do this?

This is what I have so far:

SELECT  [Title] WHEN '' THEN 'blank' ELSE ISNULL([Title],'blank') AS [Title],
        COUNT([value]) AS [Count]
FROM ....
WHERE ....
GROUP BY [Title],[Count]

but because of the Group By, they are split into two different rows:

enter image description here

Upvotes: 1

Views: 4182

Answers (4)

Thomas
Thomas

Reputation: 64674

An alternate to Joe Stefanelli's solution:

Select Case
        When Len( [Title] ) > 0 Then [Title]
        Else 'blank'
        End
From ...
Group By Case
            When Len( [Title] ) > 0 Then [Title]
            Else 'blank'
            End

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

SELECT  CASE WHEN COALESCE([Title],'') = '' THEN 'blank' 
             ELSE [Title] 
        END AS [Title],
        COUNT([value]) AS [Count]
FROM ....
WHERE ....
GROUP BY CASE WHEN COALESCE([Title],'') = '' THEN 'blank' 
              ELSE [Title] 
         END

Upvotes: 5

jack.mike.info
jack.mike.info

Reputation: 128

COALESCE

select title_id,count(*),COALESCE(type,pub_id)
from titles group by title_id,type,pub_id

Upvotes: 0

NateTheGreat
NateTheGreat

Reputation: 2305

Having the [Count] column in the GROUP BY clause seems suspicious... does it work if you only group by [Title]?

Upvotes: 0

Related Questions