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:
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 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 Having the [Count] column in the GROUP BY clause seems suspicious... does it work if you only group by [Title]?
COALESCE
select title_id,count(*),COALESCE(type,pub_id)
from titles group by title_id,type,pub_id
