[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

I want my Count() function to return 0 instead of nothing

Hi Guys,

When I do select Count(Somecolumns) from sometable between startdate and end date. and the the answer is 0, SQL does not return any rows. I want it to return with one row but value 0. How can I do that?
0
TheCommunicator
Asked:
TheCommunicator
5 Solutions
 
radcaesarCommented:
WHERE RDate BETWEEN '20011212' AND '20020105'
0
 
devlab2012Commented:
if I execute following query, the answer is 0:

select count(CompanyName) from customers where 1<>1

Sql returns one row with count = 0.
Can you provide some details of your query?
0
 
Kevin CrossChief Technology OfficerCommented:
You will need to use a conditional aggregate or use a subquery.

Using aggregate:
SELECT COUNT(CASE WHEN YourDateCol BETWEEN @startdate AND @enddate THEN SomeCol END)
FROM YourTable

Open in new window


Or
SELECT COALESCE((SELECT COUNT(SomeCol) FROM YourTable WHERE YourDateCol BETWEEN @startdate AND @enddate ), 0)

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Kevin CrossChief Technology OfficerCommented:
Though, I do agree with other Expert's post.  Didn't see it before I posted.
0
 
TheCommunicatorAuthor Commented:
Ok I tried it with  but it does not seem to work


Select 
				convert(varchar(max),convert(datetime,@StartDate_dt))  as CurrentDate,
				COALESCE(COUNT(Banner_ID_inc),0),
				Banner_Name_vch
				
			FROM 
				Advertising_Banners_History 
			WHERE
				Site_ID_int = 1
			AND
				Event_dt 
			between 
				@StartDate_dt
			AND
				 @TempEndDate
			AND
			  Banner_Name_vch = @Data
			group by  Banner_Name_vch	

Open in new window


And it still returns no rows instead of 0
                  
0
 
TheCommunicatorAuthor Commented:
I am not sure how

SELECT COUNT(CASE WHEN YourDateCol BETWEEN @startdate AND @enddate THEN SomeCol END)
FROM YourTable

Open in new window


will fit into my requirement?/
0
 
TheCommunicatorAuthor Commented:
@devlab2010: I am really not sure why it is not returning me . I am attaching a screenshot
No-rows-returned.png
0
 
TheCommunicatorAuthor Commented:
Ok i figured what the problem is. The Query is in the Group by clause and that is causing it to fail. But now the question is that how to address this problem?
0
 
Kevin CrossChief Technology OfficerCommented:
Couple of ways to solve this.  Yes, the issue is since there isn't a matching row, then grouping by data in that row results in empty recordset.

Firstly, then, you can avoid the GROUP BY.  If Banner_Name_vch = @Data, then grouping by Banner_Name_vch is meaningless right as there is only one value @Data?

How about:
SELECT CONVERT(VARCHAR(MAX), CONVERT(DATETIME,@StartDate_dt)) AS CurrentDate
     , COUNT(Banner_ID_inc)
	 , @Data AS Banner_Name_vch			
FROM Advertising_Banners_History 
WHERE Site_ID_int = 1
AND Event_dt between @StartDate_dt AND @TempEndDate
AND  Banner_Name_vch = @Data

Open in new window


Otherwise, you can try to do as I said and move the criteria that is causing there to be no results like the date range and put that as a conditional of the COUNT aggregate.

SELECT CONVERT(VARCHAR(MAX), CONVERT(DATETIME,@StartDate_dt)) AS CurrentDate
     , COUNT(CASE WHEN Event_dt between @StartDate_dt AND @TempEndDate THEN Banner_ID_inc END)
	 , Banner_Name_vch			
FROM Advertising_Banners_History 
WHERE Site_ID_int = 1
AND  Banner_Name_vch = @Data
GROUP BY Banner_Name_vch

Open in new window


As a side note, there are likely much better ways to do CONVERT(VARCHAR(MAX), CONVERT(DATETIME,@StartDate_dt)).

Hope that helps!
0
 
TheCommunicatorAuthor Commented:
@mwvisa1: The first approach that you suggested seems to work but I don't understand what exactly you did with

@Data AS Banner_Name_vch

Open in new window


statement in Select?

Btw, Lols Your suggestion indeed valuable on

CONVERT(VARCHAR(MAX), CONVERT(DATETIME,@StartDate_dt)).

Open in new window


Actually, I was returning some concatenated string with that originally and never removed the varchar conversaion  but yeah I know it looks clumsy... lols
0
 
TheCommunicatorAuthor Commented:
Actually, i got your point that when bannername = @data then only the condition is going to be true and it's a slick way to avoid Group By clause.

But on a side question,Are there any other ways that I can use to avoid the Group By problem?
0
 
Kevin CrossChief Technology OfficerCommented:
Okay.  The issue on the query returning no results versus one row of 0 as typically would expect with COUNT() is because you are grouping by a column of data in the results.  Since there aren't any results that meet the criteria, the GROUP BY causes the query to return no resultset; therefore, the first fix works by excluding a group by.  Since you were filtering by Banner_Name_vch = @Data which will result in Banner_Name_vch being the same for every row in the results, i.e., same as having a literal value in the SELECT statement, I just removed the group by and used the literal @Data which eliminates the need for GROUP BY.  Hopefully that wasn't confusing.
0
 
Kevin CrossChief Technology OfficerCommented:
The conditional aggregate approach I showed is another.  Basically setup the query so it returns data for each Banner_Name_vch even if you are counting on a more strict criteria like having an event date within a specific range -- now your query isn't an empty resultset.  Put the restriction on count in the COUNT() function like:

COUNT(CASE WHEN Event_dt between @StartDate_dt AND @TempEndDate THEN Banner_ID_inc END)

And you will get 0's when applicable.

Other way to do this is to have a query that gets the Banner_Name_vch values you want to see.  Maybe from a table that just has those or a derived table like:

(SELECT DISTINCT Banner_Name_vch FROM YourTable WHERE {some criteria is true}) t

Now you can LEFT JOIN your other table to this -- put all the filter criteria in the JOIN's ON clause and then do your COUNT.  Can do this with second table being a derived aggregate also.  You will have to use COALESCE(Cnt, 0) in that instance.
0
 
TheCommunicatorAuthor Commented:
Thank you so much:)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now