Get 0 Value from a Count with No Rows

Get 0 value from a count with no rows

You need to use the COALESCE function in PostgreSQL http://developer.postgresql.org/pgdocs/postgres/functions-conditional.html

Essentially you need to tell SQL how to handle NULLs. i.e. When NULL return 0.

return 0 in select count when no record found

Just use:

SELECT max(id) as id, count(*)
FROM table1
WHERE reference_id = 300000009798620

SQL query returns 0 for COUNT(*) if no rows

Description: You can use a recursive CTE to build your list of "dates" which you want to see. I hard-coded 5, as that was your example, you can use whatever works for you, even a variable. The LEFT JOIN will ensure that you get a record for every date in your range, and you'll get 0 for those dates which don't have data

Code Example:

WITH dateTable AS (
    SELECT 
         CAST(GETDATE() AS DATE )Date
    UNION ALL   
    SELECT 
         DATEADD(DAY,-1,dateTable.Date) Date
    FROM dateTable
    WHERE Date >= GETDATE() - 5 /*Enter your number of days to go back, here*/
)
SELECT 
     dateTable.Date
    ,COUNT(Reservation.Date)
FROM dateTable
LEFT OUTER JOIN Reservation ON dateTable.Date = Reservation.Date
GROUP by dateTable.Date
ORDER BY dateTable.Date;

Documentation: https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

Retrieving row count and returning 0 when no rows

Assuming created to be of type date for lack of information.

Postgres provides the wonderful generate_series() to make this easy:

SELECT d.created, COUNT(s.id) AS ct
FROM  (
   SELECT generate_series(min(created)
                        , max(created), interval '1 day')::date AS created
   FROM   signups
   ) d
LEFT   JOIN signups s USING (created)
GROUP  BY 1
ORDER  BY 1 DESC;

This retrieves minimum and maximum day from your table automatically and provides one row per day in between.

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.

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

---

Related Topics