SQL Server case when and group by to return a value when no rows returned

Viewed 289

I am trying to solve a problem I can't seem to find an easy solution for. I am trying to write a query that uses a case when statement to return a value based on some counts. In the same query, I also need to return an orderid as I need to utilize the data returned by this for another query.

Below is the query:

select CASE WHEN count(name) = 0 THEN '1' ELSE CONVERT(varchar,count(name) + 1) END AS box
, orderid
from order 
where len(name) = 7
and orderid = 'XYZ' 
group by orderid

The issue above is that as you can imagine, a row is only returned when a a name with length 7 exists in the data. I need this query to return a valid value regardless of that.

The query should return: 1, XYZ when no name of length 7 exists. The query does work when a valid 7 length name exists.

What would be the best way of handling this issue? Note that the data can contain multiple names that are below length 7. These names will then change to length 7 after a transaction takes place.

Below is sample data and desired results:

NAME     ORDERID
ABC      001
XYZ      001
123      001
OOP      002
LMT      002

When I run my query for orderid = '1', the query should return 1, 001. The data will be constantly changing so initially it was as pictured above. As transactions take place, data will be changed as:

    NAME           ORDERID
    ABCABCABC      001
    XYZ            001
    123            001
    OOP            002
    LMT            002

At this point, I don't have to worry about my query above because it will return a valid row: 2, 001

Issue is when the name column us not the right length, the query returns nothing.

3 Answers
Related