CODEWITHSUNDEEP
mysqlsql
Stones
Stones

Reputation: 13

In mysql it's not showing count value 0

SELECT IF(COUNT(cm.CUSTOMER_ID)>0,COUNT(cm.CUSTOMER_ID),0) COUNT
FROM  customer_master cm
JOIN customer_issue_details ci USING (customer_id)
WHERE ci.ACTUAL_DATE_RETURN < ci.RETURN_DATE
   AND CUSTOMER_NAME LIKE 'r%'
GROUP BY cm.`CUSTOMER_ID`;

When no such record is found it is not showing 0 under count but showing only alias count. How to get 0 under count.I have used ifnull,ifisnull also but of no use. Please can any one tell why this is not working and how to get that 0.

Upvotes: 0

Views: 2515

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31792

You need to use a LEFT JOIN. And every condition for any ci column must be in the ON clause (otherwise the LEFT JOIN will be converted to INNER JOIN). And you have to count ci.CUSTOMER_ID not cm.CUSTOMER_ID.

SELECT cm.CUSTOMER_ID, COUNT(ci.CUSTOMER_ID) COUNT
FROM  customer_master cm
LEFT JOIN customer_issue_details ci
  ON  ci.customer_id = cm.CUSTOMER_ID
  AND ci.ACTUAL_DATE_RETURN < ci.RETURN_DATE
WHERE CUSTOMER_NAME LIKE 'r%'
GROUP BY cm.`CUSTOMER_ID`;

The INNER JOIN (JOIN is an alias for INNER JOIN) will filter out any row from cm table if no row from the ci table has been found matching the JOIN condition (It doesn't matter if you use USING or ON). The LEFT JOIN will return at least one row from cm table, but if no row from ci table has been found matching the JOIN condition, all columns from ci table will be NULL.

Example: 

cm:
| customer_id |
|-------------|
|           1 |
|           2 |
|           3 |

ci:

| customer_id | 
|-------------|
|           1 |
|           1 |
|           2 |

INNER JOIN:

SELECT cm.customer_id as `cm.customer_id`, ci.customer_id as `ci.customer_id`
FROM cm
JOIN ci 
  ON ci.customer_id = cm.customer_id;

| customer_id | customer_id |
|-------------|-------------|
|           1 |           1 |
|           1 |           1 |
|           2 |           2 |

LEFT JOIN:

SELECT cm.customer_id, ci.customer_id
FROM cm
LEFT JOIN ci 
  ON ci.customer_id = cm.customer_id;

| customer_id | customer_id |
|-------------|-------------|
|           1 |           1 |
|           1 |           1 |
|           2 |           2 |
|           3 |      (null) |

fiddle

With GROUP BY cm.customer_id and COUNT(ci.customer_id) you can count the number of found rows each cm.customer_id.

SELECT cm.customer_id, COUNT(ci.customer_id)
FROM cm
LEFT JOIN ci 
  ON ci.customer_id = cm.customer_id
GROUP BY cm.customer_id

| customer_id | COUNT(ci.customer_id) |
|-------------|-----------------------|
|           1 |                     2 |
|           2 |                     1 |
|           3 |                     0 |

fiddle

It returns 0 for cm.customer_id = 3 because COUNT counts only values that are NOT NULL.

If you use COUNT(cm.customer_id) instead you will get 1 for cm.customer_id = 3 because it's not NULL. fiddle

Now if you have any condition for a column in ci table (like ci.customer_id < 2) and you put it in the WHERE clause, all rows that do not match that condition will be filtered out.

SELECT cm.customer_id, ci.customer_id
FROM cm
LEFT JOIN ci 
  ON  ci.customer_id = cm.customer_id
WHERE ci.customer_id < 2

| customer_id | customer_id |
|-------------|-------------|
|           1 |           1 |
|           1 |           1 |

But moving that condition into the LEFT JOIN ON clause you keep at least one row for each cm.customer_id, because this is how LEFT JOIN works.

SELECT cm.customer_id, ci.customer_id
FROM cm
LEFT JOIN ci 
  ON  ci.customer_id = cm.customer_id
  AND ci.customer_id < 2

| customer_id | customer_id |
|-------------|-------------|
|           1 |           1 |
|           1 |           1 |
|           2 |      (null) |
|           3 |      (null) |

Now GROUP BY and COUNT:

SELECT cm.customer_id, COUNT(ci.customer_id)
FROM cm
LEFT JOIN ci 
  ON  ci.customer_id = cm.customer_id
  AND ci.customer_id < 2
GROUP BY cm.customer_id;

| customer_id | COUNT(ci.customer_id) |
|-------------|-----------------------|
|           1 |                     2 |
|           2 |                     0 |
|           3 |                     0 |

sqlfiddle

Upvotes: 2

wilx
wilx

Reputation: 18228

Without testing, try using grouping not by the cm.CUSTOMER_ID but by the ci.CUSTOMER_ID. Also, I think you need a LEFT JOIN.

SELECT COUNT(ci.CUSTOMER_ID)
FROM customer_master cm
LEFT JOIN customer_issue_details ci USING (customer_id)
WHERE ci.ACTUAL_DATE_RETURN < ci.RETURN_DATE
   AND CUSTOMER_NAME LIKE 'r%'
GROUP BY ci.CUSTOMER_ID;

Here is SQL Fiddle simplified without ACTUAL_DATE_RETURN and without RETURN_DATE: http://sqlfiddle.com/#!9/73cc64/2/0

UPDATE 1

In ur fiddle if u change '%r' to '%a' it's not even showing output table but it should have given 0 under ur table name

This is how SQL works. Since there is no customer with name matching that expression, you get no result rows. You need to check for empty results set in your application.

Upvotes: 0

Related Questions