MYSQL - COUNT() NULL Values

Question

This has been racking my head. I've scoured the internet (including this place) and can't find a solution. So as a last resort I was hoping the good people of this forum might be able to help me out.

I have two tables:

TableA
Order_detailsID 
OrderID
TitleID
Return_date

TableB
TitleID
Title_name
Quantity_in_stock

And would like to run a query that shows the remaining 'Quantity_in_stock'.

If the 'Return_date' is set to NULL then it means the item is currently out -- so I have been trying to use the count() function for the NULL values and subtract it from the 'Quantity_in_stock'.

This is the script I have so far:

DELIMITER //
 CREATE PROCEDURE InStock()
BEGIN

Select TableB.TitleID,
TableB.Title_name,
TableB.Quantity_in_stock AS 'Total_Stock',
COUNT(TableA.return_date IS NULL) AS 'Rented_Out',
TableB.Quantity_in_stock - COUNT(TableA.return_date IS NULL) AS 'Remaining Stock'
From TableB
LEFT JOIN TableA
ON TableA.TitleID = TableB.TitleID
GROUP BY TableB.TitleID;

END//

This works if there is one of more of the TitleIDs at NULL, however if there are no values at NULL, then the Count() is still returning a value of 1 when it should be 0.

What am I doing wrong?

Giorgos Betsos · Accepted Answer · 2015-12-12 21:43:55Z

3

Instead of:

COUNT(TableA.return_date IS NULL)

use this:

SUM(CASE 
        WHEN TableA.TitleID IS NULL THEN 0
        WHEN TableA.return_date IS NOT NULL THEN 0
        ELSE 1
      END)

The problem with the TableA.return_date IS NULL predicate is that it's true in two completely different situations:

When there is no matching record in TableA
When there is a matching record but TableA.return_date value of this exact record is NULL.

Using the CASE expression you can differentiate between these two cases.

edited Dec 12, 2015 at 21:43

answered Dec 12, 2015 at 20:11

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

h7681 Over a year ago

Thanks for the quick response. That is working for TitleIDs that don't appear at all in TableA. However, TitleIDs that do appear in TableA but have a non-NULL return_date (a date) are still being counted. I only want to subtract the NULL values from the quantity.

h7681 Over a year ago

Appreciate the help again, but I'm afraid I'm still getting two issues: 1) If the TitleID doesn't appear in TableA at all then it's still counting it once and deducting 1 from TableB.Quantity_in_stock. 2) If the TitleID appears in TableA with a non-NULL return_date, then it's also counting these.

Giorgos Betsos Over a year ago

@Gary866 Can you provide some sample data along with required result set?

Giorgos Betsos Over a year ago

@Gary866 Please check the edit I made. I'm using SUM instead of COUNT. You can also see a use case here.

h7681 Over a year ago

@Ben it seems that values that don't appear in TableA as considered NULL, so your script adds a 1 to them and include them to the SUM(). I appreciate the response nonetheless.

|

Danyal Sandeelo · Accepted Answer · 2015-12-12 21:47:19Z

1

I will like to mention a simple concept here, just keep counting the rows when that particular column is null.

select count(*) from table_name where column_name is null

answered Dec 12, 2015 at 21:47

Danyal Sandeelo

12.4k10 gold badges57 silver badges90 bronze badges

Collectives™ on Stack Overflow

MYSQL - COUNT() NULL Values

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related