Count Returning Blank Instead of 0

Count Returning blank instead of 0

You cannot expect any records to be outputted when using a GROUP BY clause, when no records exist in your source.

If you want an output of 0 from the SUM and COUNT functions, then you should not use GROUP BY.

The reason is that when you have no records, the GROUP BY clause has nothing to group by, and then is not able to give you any output.

For example:

SELECT COUNT(*) FROM (SELECT 'Dummy' AS [Dummy] WHERE 1 = 0) DummyTable

will return one record with the value '0', where as:

SELECT COUNT(*) FROM (SELECT 'Dummy' AS [Dummy] WHERE 1 = 0) DummyTable
GROUP BY [Dummy]

will return no records.

Why count doesn't return 0 on empty table

So I read up on the grouping mechanisms of sybase, and came to the conclusion, that in your query you have a "Transact-SQL extended column" (see: docs on group by under Usage -> Transact-SQL extensions to group by and having):

A select list that includes aggregates can include extended columns that are not arguments of aggregate functions and are not included in the group by clause. An extended column affects the display of final results, since additional rows are displayed.* (emphasis mine)

(regarding the *: this last statement is actually wrong in your specific case, since one rows turn into zero rows)

also in the docs on group by under Usage -> How group by and having queries with aggregates work you'll find:

The group by clause collects the remaining rows into one group for each unique value in the group by expression. Omitting group by creates a single group for the whole table. (emphasis mine)

So essentially:

1. having a COUNT(*) will trigger the whole query to be an aggregate, since it is an aggregate function (causing an implicit GROUP BY NULL)
2. adding ID in the SELECT clause, will then expand the first group (consisting of no rows) into its contained rows (none) and join it together with the aggregate result columns.

in your case: the count is 0, since you also query for the id, for every id a row will be generated to which the count is appended. however, since your table has no rows, there are no result rows whatsoever, thus no assignments. (Some examples are in the linked docs, and since there is no id and an existing id must be in the id column of your result, ...)

to always get the count, you should probably only SELECT @ROWS = COUNT(*) and select ids separately.

Return NULL instead of 0 when using COUNT(column) SQL Server

Try this:

`select NULLIF ( Count(something) , 0)

When count is giving blank with group by how to replace it with 0 in sql

If this does not do anything you look at the group by part first.
Group by eliminates all rows and then there is no count in the group by.

to counter this condition use not exists like below

if not exists (Select  cast((count( ID)) as varchar(50)) as Total 

     from Temp1 
          where 
            quarter  in ('202203','202204') 
           group by  ID , Quarter    having count(ID) >1  )  
begin
insert into #result(total) select 0 as total
end
else
begin
insert into #result(total)
Select  cast((count( ID)) as varchar(50)) as Total 

     from Temp1 
          where 
            quarter  in ('202203','202204') 
           group by  ID , Quarter    having count(ID) >1
end

See demo below where I created an empty table

Make a query Count() return 0 instead of empty

Replace the Count statements with

Sum(Iif(DateDiff("d",DateAdded,Date())>=91,Iif(DateDiff("d",DateAdded,Date())<=180,'1','0'),'0')) AS BTWN_91_180,

I'm not a fan of the nested Iifs, but it doesn't look like there's any way around them, since DateDiff and BETWEEN...AND were not playing nicely.

To prune ItemNames without any added dates, the query block had to be enclosed in a larger query, since checking against a calculated field cannot be done from inside a query. The end result is this query:

SELECT *
FROM 
     (
     SELECT DISTINCT Source.ItemName AS InvestmentManager, 
     Sum(Iif(DateDiff("d",DateAdded,Date())>=20,Iif(DateDiff("d",DateAdded,Date())<=44,'1','0'),'0')) AS BTWN_20_44,
     Sum(Iif(DateDiff("d",DateAdded,Date())>=45,Iif(DateDiff("d",DateAdded,Date())<=60,'1','0'),'0')) AS BTWN_45_60,
     Sum(Iif(DateDiff("d",DateAdded,Date())>=61,Iif(DateDiff("d",DateAdded,Date())<=90,'1','0'),'0')) AS BTWN_61_90,
     Sum(Iif(DateDiff("d",DateAdded,Date())>=91,Iif(DateDiff("d",DateAdded,Date())<=180,'1','0'),'0')) AS BTWN_91_180,
     Sum(Iif(DateDiff("d",DateAdded,Date())>180,'1','0')) AS GT_180,
     Sum(Iif(DateDiff("d",DateAdded,Date())>=20,'1','0')) AS Total
     FROM Source
     WHERE CompleteState='FAILED'
     GROUP BY ItemName
     )
WHERE Total > 0;

blank instead of zero

If your function returns an integer the result from isnull will also be an integer. In the case the return value is null you will have an implicit conversion to integer for '' and that will be 0.

Try this:

declare @xx int
select isnull(@xx,'')

Result:

-----------
0

You can have the space if you first cast the return value from your function to varchar.

declare @xx int
select isnull(cast(@xx as varchar(10)),'')

Result:

----------
                                                                                .

If your function returns 0 instead of null you can use nullif to get a null value before you cast to varchar.

declare @xx int = 0
select isnull(cast(nullif(@xx, 0) as varchar(10)),'')

Summary:

You need this:

Duration = isnull(cast(FunctionA(DateA,DateB) as varchar(10)),'')

or this

Duration = isnull(cast(nullif(FunctionA(DateA,DateB), 0) as varchar(10)),'')

Return blank instead of 0 in sql case when statement

It returns zero because the first THEN expression of the CASE is a number (VALUE), and the compiler converts the second expression (ELSE '') to a number also, to make the results of all CASE branches compatible.

You could try returning string for both (THEN and ELSE parts), instead:

, CASE WHEN [VALUE]>0 THEN CAST(VALUE as varchar(50)) ELSE '' END as VALUE1

However, I can't tell if this is the right approach for you because the VALUE may have fractional/exponential parts, and simple cast to varchar(50) may not show them the way you want to show. If that is the case then you can try the FORMAT function (if you are using SQL Server), instead of CAST (FORMAT should also return VARCHAR)

Return 0 when SELECT COUNT is null

Do you want a group by in the query?

SELECT m.id, m.name, m.description, m.directions, COUNT(j.markerid) as marker_jingles
FROM markers AS m LEFT OUTER JOIN
     jingles AS j
     ON j.markerid=m.id
WHERE 1
GROUP BY m.id;

Your original query should always return one row, with the count of all matches after the join. The count() value should not be NULL -- unless you are using a very old version of MySQL.

LINQ Count returning 1 instead of zero for an empty group

SQL COUNT function ignores the NULL values, while LINQ Count function w/o predicate counts everything, including nulls.

You can get the same result in LINQ by using the predicate version of Count like this (note the group docIfNull so the g elements will be of the same type as docIfNull):

from oy in OwnerYears
join doc in VaStDocuments on new { oy.OwnerId, oy.Year } equals new { doc.OwnerId, doc.Year } into docS
from docIfNull in docS.DefaultIfEmpty()
group docIfNull by new { oy.OwnerId, oy.Year } into g
let docCount = g.Count(doc => doc != null)
orderby docCount ascending
select new { OwnerId = g.Key.OwnerId, Year = g.Key.Year, docCount = docCount }

(the let clause is just to reuse the expression in orderby and select).

However in LINQ you have another option - in case the (OwnerId, Year) combination inside OwnerYears is unique as it seems, instead of left outer join pattern followed by group by and Count filtering nulls you could use simple group join operator with regular Count call:

from oy in OwnerYears
join doc in VaStDocuments on new { oy.OwnerId, oy.Year } equals new { doc.OwnerId, doc.Year } into docs
let docCount = docs.Count()
orderby docCount ascending
select new { OwnerId = oy.OwnerId,  Year = oy.Year, docCount = docCount }

---

Related Topics