CODEWITHSUNDEEP
sqlsql-servert-sqlnull
Komal Bachhuka
Komal Bachhuka

Reputation: 75

In SQL, how to check for null condition on result with more than 1 value?

I want to get orderdate and productid in the result if that product has been ordered more than once on that day. If there is no product ordered more than once on any date, I want to return null in both columns.

I have figured out the result where the happy case of at least one product being ordered more than once exists in the table using the having clause. If I try using isnull or coalesce, I get an error since my final result has two values (date and productID)

select orderdate, prodid
from orders
group by orderdate, prodid
having count(*) > 1

On using isnull on the above query, I get this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 1

Views: 225

Answers (1)

forpas
forpas

Reputation: 164064

Use UNION ALL to get the row with nulls if there are no results from your query:

with cte as (
  select orderdate, prodid
  from orders
  group by orderdate, prodid
  having count(*)>1
)

select * from cte
union all
select null, null
where not exists (
  select 1 from cte
)

Upvotes: 3

Related Questions