Question 246954 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get 0 instead of 'NaN' in SELECT COUNT(*) request?

I need to count quantity of some condition and avoid ‘NaN’ in result.
I tried ways with CASE and COALESCE constructions but have no needed result:

-- 1st: it works with 'NaN' in part of results:
SELECT response_date, 
    COUNT(*) AS views_per_day
FROM tab
WHERE v_result = condition
GROUP BY response_date
ORDER BY response_date

-- 2nd: it works with no effect, exact like first code:
SELECT response_date, 
    COALESCE(COUNT(*), 0) AS views_per_day
FROM tab
WHERE v_result = condition
GROUP BY response_date
ORDER BY response_date

-- 3rd: it doesn't work:
SELECT response_date, 
    CASE 
        WHEN COUNT(*) = 'NaN' THEN 0
        ELSE COUNT(*)
    END AS views_per_day
FROM tab
WHERE v_result = condition
GROUP BY response_date
ORDER BY response_date

UPDATE
enter image description here

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    So, the problem was not in the request. This request is a part of much bigger SQL-request. And I thought the problem goes from SQL response. In fact, the problem was in handling in Python's script - I had no direct acsess to DB and did it through script. At one of stages of SQL request I did a join of tables - and the data's gap arised there. Already Python's script interprets NULL of SQL as 'NaN' and display it. It turned out that the solution is very simple:

    SELECT COALESCE(views_per_day, 0) AS views_per_day

    Thanks to all who helped me to clarify the details.


  2. 0

    You can use Subquery

    SELECT response_date,
    COUNT(*) AS views_per_day

    FROM tab
    WHERE v_result in (SELECT v_result FROM tab is not null)

    GROUP BY response_date

    ORDER BY response_date

    Login or Signup to reply.
  3. 0

    In MsSQL we can use ISNULL():

    SELECT response_date, ISNULL(COUNT(*), 0) AS views_per_day
FROM tab
WHERE v_result = condition
GROUP BY response_date
ORDER BY response_date
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search