How to return something when there is no record

Question

How to return something when there is no record

halifaxdal

SSCoach

Points: 19741
More actions
February 25, 2014 at 9:39 am

#396320

Hello,
I have an existing query:
SELECT
i.Division,
DocStatus,
SUM(CASE WHEN RiskRating = 3 THEN 1 ELSE 0 END),
SUM(CASE WHEN RiskRating = 2 THEN 1 ELSE 0 END),
SUM(CASE WHEN RiskRating = 1 THEN 1 ELSE 0 END)
FROM Document d
Inner join ITSDivision i on d.ITSDivisionID = i.ID
Where
DocType = 1 and --Deviation only
VPID <> -1 and --With VP assigned
CAST(YEAR(DateFirstIssued) AS int) = @Year and
CAST(DATENAME(QUARTER, DateFirstIssued) AS int) = @Quarter --For Quarter
and i.Division = @Division and DocStatus = @DocStatus
GROUP BY
i.Division,
DateFirstIssued,
DATEADD(QUARTER, DATEDIFF(QUARTER, 0, DateFirstIssued), 0),
DocStatus
In some cases (@Division and @DocStatus and @Year/@Quarter), there might be no result, however, I still want to return 0 for the three SUM
How do I make it?
Thank you very much.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Joey Morgan SSC-Addicted Points: 458 More actions · Answer 1

halifaxdal (2/25/2014)
Hello,
I have an existing query:
SELECT
i.Division,
DocStatus,
. . .
ISNULL(SUM(CASE WHEN RiskRating = 3 THEN 1 ELSE 0 END),0)
. . .
In some cases (@Division and @DocStatus and @Year/@Quarter), there might be no result, however, I still want to return 0 for the three SUM
How do I make it?
Thank you very much.

The problem you are having is that by default the sum of anything and null is null, not zero. When that is the case you want to handle the null by making it a zero.

Hope this helps,

Joey

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Joey Morgan (2/25/2014)
halifaxdal (2/25/2014)
Hello,
I have an existing query:
SELECT
i.Division,
DocStatus,
ISNULL(SUM(CASE WHEN RiskRating = 3 THEN 1 ELSE 0 END),0),
ISNULL(SUM(CASE WHEN ISNULL(RiskRating,0) = 2 THEN 1 ELSE 0 END),0),
ISNULL(SUM(CASE WHEN ISNULL(RiskRating,0) = 1 THEN 1 ELSE 0 END),0)
FROM Document d
Inner join ITSDivision i on d.ITSDivisionID = i.ID
Where
DocType = 1 and --Deviation only
VPID <> -1 and --With VP assigned
CAST(YEAR(DateFirstIssued) AS int) = @Year and
CAST(DATENAME(QUARTER, DateFirstIssued) AS int) = @Quarter --For Quarter
and i.Division = @Division and DocStatus = @DocStatus
GROUP BY
i.Division,
DateFirstIssued,
DATEADD(QUARTER, DATEDIFF(QUARTER, 0, DateFirstIssued), 0),
DocStatus
In some cases (@Division and @DocStatus and @Year/@Quarter), there might be no result, however, I still want to return 0 for the three SUM
How do I make it?
Thank you very much.
The problem you are having is that the sum of anything and null is null, not zero. When that is the case you want to handle the null by making it a zero.
Hope this helps,
Joey

That isn't the issue here. Notice the SUM has a case expression inside which will return either 1 or 0.

I think the issue is that the OP is not getting rows when she is expecting one?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Joey Morgan SSC-Addicted Points: 458 More actions · Answer 3

If she gets any rows, she will get a zero trhough the case statement; that's correct. But if there are no rows she will only get null, and that's what this solves.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

Joey Morgan (2/25/2014)
If she gets any rows, she will get a zero trhough the case statement; that's correct. But if there are no rows she will only get null, and that's what this solves.

It's irrelevant. If there are no rows returned, there's nothing to have a value, null or otherwise.

A quick solution for this is to construct a single-row derived table using all of the variables and left-join the tables to it.

FROM (SELECT Quarter = @Quarter....) d

LEFT JOIN...

and join your tables to d.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

halifaxdal SSCoach Points: 19741 More actions · Answer 5

Isnull won't work in my case as the where clause doesn't return any record.

I ended up using

if not exists (select ....)

insert into

select.....

insert into

select 0,0....

But I believe there should be a more efficient way to do the same thing.

Thanks for discussion.

Joey Morgan SSC-Addicted Points: 458 More actions · Answer 6

As proof of the technique, I offer...

DECLARE @TestTable TABLE

(

id INT

,testValue NVARCHAR(10)

,someNumber INT

);

INSERT INTO @testTable

(id, testValue, someNumber)

VALUES

(1, 'First', 10);

INSERT INTO @testTable

(id, testValue, someNumber)

VALUES

(2, 'Second', 3);

INSERT INTO @testTable

(id, testValue, someNumber)

VALUES

(3, 'third', 10);

INSERT INTO @testTable

(id, testValue, someNumber)

VALUES

(4, 'fourth', 3);

SELECT

SUM(CASE WHEN testvalue = 'first' THEN 1

ELSE 0

END) AS FirstTotal

,SUM(CASE WHEN testvalue = 'second' THEN 1

ELSE 0

END) AS SecondTotal

,SUM(CASE WHEN testvalue = 'third' THEN 1

ELSE 0

END) AS ThirdTotal

,SUM(CASE WHEN testvalue = 'fourth' THEN 1

ELSE 0

END)AS FourthTotal

FROM

@TestTable AS TT

WHERE id <=2;

SELECT

SUM(CASE WHEN testvalue = 'first' THEN 1

ELSE 0

END)AS FirstTotal

,SUM(CASE WHEN testvalue = 'second' THEN 1

ELSE 0

END) AS SecondTotal

,SUM(CASE WHEN testvalue = 'third' THEN 1

ELSE 0

END) AS ThirdTotal

,SUM(CASE WHEN testvalue = 'fourth' THEN 1

ELSE 0

END)AS FourthTotal

FROM

@TestTable AS TT

WHERE id >=6;

SELECT

ISNULL(SUM(CASE WHEN testvalue = 'first' THEN 1

ELSE 0

END),0)AS FirstTotal

,ISNULL(SUM(CASE WHEN testvalue = 'second' THEN 1

ELSE 0

END),0) AS SecondTotal

,ISNULL(SUM(CASE WHEN testvalue = 'third' THEN 1

ELSE 0

END),0) AS ThirdTotal

,ISNULL(SUM(CASE WHEN testvalue = 'fourth' THEN 1

ELSE 0

END),0) AS FourthTotal

FROM

@TestTable AS TT

WHERE id >=6;

The results are:

FirstTotal SecondTotal ThirdTotal FourthTotal

----------- ----------- ----------- -----------

1 1 0 0

FirstTotal SecondTotal ThirdTotal FourthTotal

----------- ----------- ----------- -----------

NULL NULL NULL NULL

FirstTotal SecondTotal ThirdTotal FourthTotal

----------- ----------- ----------- -----------

0 0 0 0

😎

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Joey Morgan (2/25/2014)
As proof of the technique, I offer...
DECLARE @TestTable TABLE
(
id INT
,testValue NVARCHAR(10)
,someNumber INT
);
INSERT INTO @testTable
(id, testValue, someNumber)
VALUES
(1, 'First', 10);
INSERT INTO @testTable
(id, testValue, someNumber)
VALUES
(2, 'Second', 3);
INSERT INTO @testTable
(id, testValue, someNumber)
VALUES
(3, 'third', 10);
INSERT INTO @testTable
(id, testValue, someNumber)
VALUES
(4, 'fourth', 3);
SELECT
SUM(CASE WHEN testvalue = 'first' THEN 1
ELSE 0
END) AS FirstTotal
,SUM(CASE WHEN testvalue = 'second' THEN 1
ELSE 0
END) AS SecondTotal
,SUM(CASE WHEN testvalue = 'third' THEN 1
ELSE 0
END) AS ThirdTotal
,SUM(CASE WHEN testvalue = 'fourth' THEN 1
ELSE 0
END)AS FourthTotal
FROM
@TestTable AS TT
WHERE id <=2;
SELECT
SUM(CASE WHEN testvalue = 'first' THEN 1
ELSE 0
END)AS FirstTotal
,SUM(CASE WHEN testvalue = 'second' THEN 1
ELSE 0
END) AS SecondTotal
,SUM(CASE WHEN testvalue = 'third' THEN 1
ELSE 0
END) AS ThirdTotal
,SUM(CASE WHEN testvalue = 'fourth' THEN 1
ELSE 0
END)AS FourthTotal
FROM
@TestTable AS TT
WHERE id >=6;
SELECT
ISNULL(SUM(CASE WHEN testvalue = 'first' THEN 1
ELSE 0
END),0)AS FirstTotal
,ISNULL(SUM(CASE WHEN testvalue = 'second' THEN 1
ELSE 0
END),0) AS SecondTotal
,ISNULL(SUM(CASE WHEN testvalue = 'third' THEN 1
ELSE 0
END),0) AS ThirdTotal
,ISNULL(SUM(CASE WHEN testvalue = 'fourth' THEN 1
ELSE 0
END),0) AS FourthTotal
FROM
@TestTable AS TT
WHERE id >=6;
The results are:
FirstTotal SecondTotal ThirdTotal FourthTotal
----------- ----------- ----------- -----------
1 1 0 0
FirstTotal SecondTotal ThirdTotal FourthTotal
----------- ----------- ----------- -----------
NULL NULL NULL NULL
FirstTotal SecondTotal ThirdTotal FourthTotal
----------- ----------- ----------- -----------
0 0 0 0
😎

In this case your technique will work. However, in the original query there was an additional column that is used as the group by.

Add the ID column to your output and this returns 0 rows which is what the OP is having problems with.

SELECT SUM(CASE WHEN testvalue = 'first' THEN 1 ELSE 0 END)AS FirstTotal

,SUM(CASE WHEN testvalue = 'second' THEN 1 ELSE 0 END) AS SecondTotal

,SUM(CASE WHEN testvalue = 'third' THEN 1 ELSE 0 END) AS ThirdTotal

,SUM(CASE WHEN testvalue = 'fourth' THEN 1 ELSE 0 END)AS FourthTotal

,ID

FROM @TestTable AS TT

WHERE id >=6

group by ID;

SELECT ISNULL(SUM(CASE WHEN testvalue = 'first' THEN 1 ELSE 0 END),0)AS FirstTotal

,ISNULL(SUM(CASE WHEN testvalue = 'second' THEN 1 ELSE 0 END),0) AS SecondTotal

,ISNULL(SUM(CASE WHEN testvalue = 'third' THEN 1 ELSE 0 END),0) AS ThirdTotal

,ISNULL(SUM(CASE WHEN testvalue = 'fourth' THEN 1 ELSE 0 END),0) AS FourthTotal

,ID

FROM @TestTable AS TT

WHERE id >=6

group by ID;

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Joey Morgan SSC-Addicted Points: 458 More actions · Answer 8

Joey Morgan

SSC-Addicted

Points: 458

February 25, 2014 at 2:42 pm

#1692072

Drat. You're right.

Oh well...

:ermm:

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Joey Morgan (2/25/2014)
Drat. You're right.
Oh well...
:ermm:

Your solution is a good one when all the data is aggregated though. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

Chris M already gave a solution, but with no DDL and sample data, I'm not giving an example.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 11

Luis Cazares (2/25/2014)
Chris M already gave a solution, but with no DDL and sample data, I'm not giving an example.

Agreed. There's also the subject of the code being non-SARGable, as well.

@halifaxdal,

See the first link under "Helpful Links" in my signature line and post some readily consumable data in the fashion of that article and we'll be much more able to help you solve the problem of missing rows as well as the performance problem associated with non-SARGable criteria.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Luis Cazares (2/25/2014)
Chris M already gave a solution, but with no DDL and sample data, I'm not giving an example.

It's not possible to give a good example without table aliases - with them, this query could be quite a lot simpler;

SELECT

i.Division,

DocStatus,

SUM(CASE WHEN RiskRating = 3 THEN 1 ELSE 0 END),

SUM(CASE WHEN RiskRating = 2 THEN 1 ELSE 0 END),

SUM(CASE WHEN RiskRating = 1 THEN 1 ELSE 0 END)

FROM (

SELECT Division = @Division, DocStatus = @DocStatus, [Year] = @Year, [Quarter] = @Quarter

) m

LEFT JOIN (

SELECT

i.Division,

DocStatus,

SUM(CASE WHEN RiskRating = 3 THEN 1 ELSE 0 END),

SUM(CASE WHEN RiskRating = 2 THEN 1 ELSE 0 END),

SUM(CASE WHEN RiskRating = 1 THEN 1 ELSE 0 END)

FROM Document d

INNER JOIN ITSDivision i on i.ID = d.ITSDivisionID

WHERE DocType = 1 --Deviation only

AND VPID <> -1 --With VP assigned

AND i.Division = @Division

AND DocStatus = @DocStatus

-- as Jeff pointed out, this part isn't SARGable. Don't filter like this

-- because performance is likely to be sucky.

-- Do it properly - calculate the first date of the year/quarter as StartRange

-- and the first date of the year/next quarter as EndRange.

-- You could use a CROSS APPLY block for this or use variables.

-- Then use DateFirstIssued >= StartRange and DateFirstIssued < EndRange

AND CAST(YEAR(DateFirstIssued) AS int) = @Year

AND CAST(DATENAME(QUARTER, DateFirstIssued) AS int) = @Quarter --For Quarter

GROUP BY

i.Division,

DocStatus,

DateFirstIssued,

DATEADD(QUARTER, DATEDIFF(QUARTER, 0, DateFirstIssued), 0)

) d ON d.Division = m.Division AND d.DocStatus = m.DocStatus AND d.[Year] = m.[Year] AND d.[Quarter] = m.[Quarter]

-- As an aside,

-- try to find the simplest way of performing actions. Compare these:

SELECT CAST(DATENAME(QUARTER, GETDATE()) AS int)

SELECT DATEPART(QUARTER,GETDATE())

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden