Return a single row if no rows are found

Question

Have this T-SQL query that we used for a CrystalReport.

SELECT COUNT(*) AS Expr1, [Date], StoreNumber
FROM   dbo.Orderp
WHERE  (OpServerNumber = 0)
GROUP BY [Date], StoreNumber

Problem occurs if no rows are valid for a specific date and store.

Is it possible to return a single row with Expr1 = 0 if the query can't find any rows I the table?

Answer 1

If you have this encapsulated in a stored procedure, you could:

• select these rows into a table variable or temporary table
• check the @@ROWCOUNT and if it's IF @@ROWCOUNT = 0, then explicitly add a dummy row to that temporary table
• return the contents from the temporary table in a SELECT * FROM ..... as the result of your stored proc

Answer 2

SELECT ISNULL(B.num,0) AS Expr1, A.[Date], A.StoreNumber
FROM 
(SELECT [Date], StoreNumber FROM dbo.Orderp GROUP BY [Date], StoreNumber) A
LEFT OUTER JOIN
(SELECT COUNT(*) AS num, [Date], StoreNumber
FROM   dbo.Orderp
WHERE  (OpServerNumber = 0)
GROUP BY 
[Date], StoreNumber) B ON A.[Date]=B.[Date] AND A.StoreNumber=B.StoreNumber

Edit: I just thought of another one...

SELECT
SUM(Expr1) AS Expr1, [Date], StoreNumber
FROM
(SELECT 0 AS Expr1, [Date], StoreNumber, NULL AS OpServerNumber
FROM   dbo.Orderp
GROUP BY [Date], StoreNumber
UNION ALL
SELECT 1, [Date], StoreNumber, OpServerNumber
FROM   dbo.Orderp)T
WHERE OpServerNumber IS NULL OR OpServerNumber = 0
GROUP BY [Date], StoreNumber

Answer 3

SELECT 
    COUNT(*) AS Expr1, [Date], StoreNumber 
FROM dbo.Orderp WHERE  (OpServerNumber = 0) GROUP BY [Date], StoreNumber

if @@ROWCOUNT = 0
    SELECT 0 AS Expr1, NULL [Date], 0 StoreNumber 

Answer 4

select Expr1, [Date], StoreNumber from (
    select *,row_number() over (order by isrealrow desc) rownum from (
        select COUNT(*) as Expr1,[Date], StoreNumber, 1 as isrealRow FROM  dbo.Orderp
        WHERE  (OpServerNumber = 0)
        GROUP BY [Date], StoreNumber
        union
        select 0, NULL, NULL, 0 as isrealrow 
    ) b
)c
where isrealRow=1 or rownum=1

That's the coolest SQL I've written all day.

Answer 5

You can user EXISTS condition if you want atleast one row like below :

IF EXISTS(SELECT COUNT(*) AS Expr1, [Date], StoreNumber FROM   dbo.Orderp WHERE  (OpServerNumber = 0) GROUP BY [Date], StoreNumber)
    SELECT COUNT(*) AS Expr1, [Date], StoreNumber FROM   dbo.Orderp WHERE  (OpServerNumber = 0) GROUP BY [Date], StoreNumber
ELSE
    SELECT 0 AS Expr1, NULL [Date], 0 StoreNumber 

Answer 6

The problem is you are using count(*) and other columns in single select.

query will get at least a row if it satisfies where clause.

You need to separate count(*) and columns only queries.