T SQL Query to get result when no rows returned

Question

this is the query

SELECT NAME,DATE, COUNT(*) AS ROW_COUNT 
FROM STG
WHERE 
DATE='2018-04-23' AND 
NAME='DRAFT'
GROUP BY NAME,DATE

Requirement is if the query returns no rows then I should get output as 'TEST','TODAY'S DATE', 0 else if query return rows then the normal output which is expected.

Gordon Linoff · Accepted Answer · 2018-04-24 01:42:08Z

1

Well, one method uses union all:

with t as (
      SELECT NAME, DATE, COUNT(*) AS ROW_COUNT 
      FROM STG
      WHERE DATE = '2018-04-23' AND NAME = 'DRAFT'
      GROUP BY NAME, DATE
     )
select *
from t
union all
select name, date, row_count
from (select 'TEST' as name, getdate() as date, 0 as row_count) x
where not exists (select 1 from t);

answered Apr 24, 2018 at 1:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NMB Over a year ago

Thanks for the swift response and help it worked perfectly!!

Collectives™ on Stack Overflow

T SQL Query to get result when no rows returned

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related