Count Data as Zero If It Is Null When Where Clause Is Used

Count data as zero if it is null when where clause is used

Your where condition on the outer joined table turns the outer join into an inner join (because the "non-existing rows will have a NULL value and the comparison of NULL with something else yields "undefined" and thus will remove that row from the result)

You need to move that condition into the join condition:

SELECT opp.name as name,
       count(log.stage_id) as stage_count
FROM crm_lead as opp 
  LEFT JOIN crm_lead_stage_log as log 
         ON opp.id = log.opportunity_id
        AND log.create_date > '2014-01-28 08:49:03'
GROUP BY name;

Why does zero results in COUNT appear or not when placing the condition in WHERE or LEFT JOIN?

It's just not true that "placing conditions in WHERE or in JOIN does not matter". If a FROM has only inner JOINs at the top level (INNER JOIN ON, JOIN ON, CROSS JOIN) then you can move conditions between its inner JOINs and WHERE. But the same is not true for OUTER JOIN. See CROSS JOIN vs INNER JOIN in SQL Server 2008 re inner joins and Conditions in LEFT JOIN (OUTER JOIN) vs INNER JOIN re outer & inner joins together.

An OUTER JOIN ON returns what INNER JOIN ON returns plus possibly more rows formed by extending input rows by NULLs. If you remove those additional rows later via a WHERE condition then you could have instead just used an INNER JOIN ON (with or) without the WHERE condition or just used a CROSS JOIN with the WHERE condition.

A CROSS JOIN does a cross join of its argument tables, an (INNER) JOIN ON does a CROSS JOIN then drops rows failing its ON condition, and an outer JOIN ON does an INNER JOIN ON then per LEFT/RIGHT/FULL adds certain rows formed by NULL-extending rows failing its ON condition. Then after all the joins WHERE drops rows failing its condition. Then SELECT drops, adds and renames columns.

SQL Count NULL values is 0 even when there are NULL values

The reason for that is because you have provided count with a column value which is null. Instead, use count(*):

SELECT COUNT(*) FROM Table_Name WHERE [Current_Status] IS NULL

---

Sample data:

current_status
--------------
Processed
Null
Not Processed
Null

And the difference between two queries:

count(current_status)

SELECT count(current_status) FROM table_name WHERE current_status IS NULL 

0

count(*)

SELECT count(*) FROM table_name WHERE current_status IS NULL 

2

if count value is null set it to zero - sql select statement

In the outer query, you can replace a NULL with a zero using the IFNULL() function, e.g.

SELECT ...
     , IFNULL(v.t1count,0) AS t1count
  FROM ...
  LEFT
  JOIN ( SELECT ... AS t1count
                ...
       ) v
    ON ...

---

The NULL you are getting returned by the outer query isn't from the inline view query. The NULL is a result of "no match" being found by the LEFT [OUTER] JOIN operation.

If you are referencing v.t1count in other expressions in the outer query, you can replace those references with NULLIF(v.t1count,0) as well.

SQL where clause not returning rows where count equals zero

The WHERE clause turns this join into an inner join, because in outer joined rows B.STATUS is null.

Change WHERE to AND in order to move the filter on the status column to the join condition for the outer join:

SELECT      A.DESCRIPTION AS WO_TEMPLATE, COUNT(B.WORKORDERID) AS NUM_OF_WOS
FROM        WOTEMPLATE AS A
LEFT JOIN   WORKORDER AS B
ON          A.WOTEMPLATEID = B.WOTEMPLATEID
AND         B.STATUS != 'CLOSED'
GROUP BY    A.DESCRIPTION
ORDER BY    WO_TEMPLATE;

showing Zero if sql count is NULL

I'm not entirely sure I understand the question, but I think you want this:

select count(codes.lcfruh) as front_lcfruh,
       dienstplan.kw,
       dienstplan.datum
from dienstplan
  left join codes on dienstplan.schicht = codes.lcfruh and codes.lcfruh <> ''
  left join personal on personal.perso_id = dienstplan.perso_id
and   personal.status = 'rezeption'
and   dienstplan.kw = $kw
group by dienstplan.datum, dienstplan.kw

If schicht comes from dienstplan there will always be a row for that (as that is the driving table). If I understand you correctly you want a 0 if no matching rows are found. Therefor you need to count the joined table.

Edit:

The condition where codes.lcfruh != '' turns the outer join back into an inner join because any "outer" row will have lcfruh as NULL and any comparison with NULL yields "unknown" and therefor the rows are removed from the final result. If you want to exclude rows in the codes table where the lcfruh has an empty string, you need to move that condition into the JOIN clause (see above).

And two more things: get used to prefixing your columns in a query with more than one table. That avoids ambiguity and makes the query more stable against changes. You should also understand the difference between number literals and string literals 1 is a number '1' is a string. It's a bad habit to use string literals where numbers are expected. MySQL is pretty forgiving as it always try to "somehow" work but if you ever user other DBMS you might get errors you don't understand.

Additionally your usage of group by is wrong and will lead to "random" values being returned. Please see these blog posts to understand why:

• http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html
• http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/

Every other DBMS will reject your query the way it is written now (and MySQL will as well in case you turn on a more ANSI compliant mode)

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 

---

Related Topics