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?

Is StoreNumber a foreign key to another table? In other words, does every store need to represented in the report even if it doesn't have an entry for a particular date? — 8kb, Aug 31 '11 at 05:50
Do you really need to solve this in SQL, as opposed to changing whatever is consuming the result set to react to no rows being returned? — Damien_The_Unbeliever, Aug 31 '11 at 06:36

score 4 · Answer 1 · answered Aug 31 '11 at 05:24

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

score 0 · Answer 2 · answered Aug 31 '11 at 05:21

0

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

answered Aug 31 '11 at 05:21

marc_s

732,580
175
1,330
1,459

sorry, it's long process to store in temp table and check row count and then add dummy row. it's possible to check in exist condition only. – Upendra Chaudhari Aug 31 '11 at 05:33
@upendra chaudhari: it depends on how many rows you have - if you have a few hundred, you won't notice any difference. Only if you had millions or tens of millions, then maybe the time needed to store temporarily would be a factor. – marc_s Aug 31 '11 at 05:34
here it's not matter of how many rows and performance issue on multiple rows. it just need to check wether any row match or not. we just need to check for single row only and it's possible using Exists condition as like in my answer. – Upendra Chaudhari Aug 31 '11 at 05:37

score 0 · Answer 3 · answered Aug 31 '11 at 05:37

0

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.

answered Aug 31 '11 at 05:37

Rahul

1,403
4
18
31

score 0 · Answer 4 · answered Aug 31 '11 at 05:51

0

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

answered Aug 31 '11 at 05:51

anaconda

1,065
10
20

score 0 · Answer 5 · answered Aug 31 '11 at 05:52

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.

Paul Walls · Answer 6 · 2011-08-31T06:49:40.093

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

Return a single row if no rows are found

6 Answers6

Linked