SQL - Returning All Rows Even If Count Is Zero for Item

SQL - Returning all rows even if count is zero for item

Modify the second query

SELECT Tree.Type as 'Requirement Type',
       COUNT(CASE WHEN [Tree].[creationDate] >= ('2010-01-01') and [Tree].[creationDate] < ('2020-01-01') THEN Tree.Type END) AS 'Number in Creation Range'
FROM [Tree] 
INNER JOIN @ReqType As RT on RT.Type = Tree.Type
INNER JOIN [Project_INFO]  ON [Project_INFO].[ProjectID]=[Tree].[Project_ID] 
INNER JOIN @CreationCount AS CCount ON CCount.BaselineID=Tree.Baseline_ID 
WHERE [Project_INFO].[Name] = 'Address Book' AND CCount.Name = 'Current Baseline' 
GROUP BY tree.Type

How to include zero / 0 results in COUNT aggregate?

You want an outer join for this (and you need to use person as the "driving" table)

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM person 
  LEFT JOIN appointment ON person.person_id = appointment.person_id
GROUP BY person.person_id;

The reason why this is working, is that the outer (left) join will return NULL for those persons that do not have an appointment. The aggregate function count() will not count NULL values and thus you'll not get a zero.

If you want to learn more about outer joins, here is a nice tutorial: http://sqlzoo.net/wiki/Using_Null

SQL COUNT returns No Data when the result should be 0

There's a few things to change from your very close attempt:

1. Move your where condition for barcelona into the ON for your join of that table instead. Otherwise your LEFT OUTER JOIN becomes an implicit INNER JOIN
2. Also instead of Count(*), just grab the count for b.host where a NULL for that column won't figure into the count.
3. Lastly swap the order in which you are joining these tables. You want all values from TableA and only those from TableB that meet your criteria.

---

SELECT A.timestamp, Count(b.host)
FROM tableA as A
LEFT JOIN tableB as B ON A.host = B.host AND B.location= 'Barcelona'
WHERE A.current_state != 0 
GROUP BY A.timestamp

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.

Display Rows that Have a 0 count

You have problem in you EventType ON PortEvent.EventID = Event.ID condition.The query below must solve your issue.

SELECT ET.Name AS [Incident Name], COUNT(E.ID) AS [Occurrence]
FROM EventType ET
  LEFT JOIN PortEvent PE
    ON ET.ID = PE.EventID
  LEFT JOIN Event E
    ON PE.EventID = E.ID
      AND E.IsIncident = 1
      AND E.Date BETWEEN ([Start of Year] AND Now)
GROUP BY ET.Name

UPDATE

If you want to include in result incidents only you can try this:

    ON PE.EventID = E.ID
      AND E.Date BETWEEN ([Start of Year] AND Now)
   WHERE E.IsIncident = 1
GROUP BY ET.Name

SQL-Query return zero count if no records are found

Taking a look at the documentation, you can use ISNULL:

ISNULL ( check_expression , replacement_value )

SELECT ISNULL(COUNT(comment), 0)) AS total
FROM dbo.omment
WHERE resp = MMColParam2 

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 zero if no results using COUNT-GROUP BY

Assuming you have some data in the table for the months you want, you can switch to conditional aggregation:

select datename(month, date_occu) as MonthName, 
       datepart(month, date_occu) as MonthNumber,
       sum(case when agency = 'WCSO' and offense = 'DEATH INVESTIGATION' then 1 else 0 end) as Quantity
from crimes.rms4gis.dbo.tmprms4gisall 
where datediff(month, date_occu, getdate()) between 1 and 3 
group by datename(month, date_occu), datepart(month, date_occu)
order by MonthNumber;

There are other methods that involve generating the three months involved. However, that requires a somewhat more complex query. The above is a simple solution that should work in this -- and many other -- situations.

Return 0 in GROUP BY when COUNT(*) is NULL

So I flipped the aggregates from the edit to my original post and now it's working:

Query

SELECT
        CAST(a.IndexedDate as varchar) as dt,
        COUNT(EventType) AS Logins
FROM 
        (
        SELECT DISTINCT(IndexedDate)
        FROM Table
        WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
        ) a
FULL OUTER JOIN (
        SELECT *
        FROM Table
        WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
        AND EventType = 'Login'
        ) b
ON 
        a.IndexedDate = b.IndexedDate
GROUP BY
        a.IndexedDate
ORDER BY
        a.IndexedDate DESC

Results

2016-09-13    41
2016-09-12    31
2016-09-11    0
2016-09-10    0
2016-09-09    15
2016-09-08    36

Note that I had to replace COUNT(*) with COUNT(EventType) so it wouldn't count the date from the aggregate which was resulting in a 1.

---

Related Topics