Question 195124 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – I am checking null values for Ecommerce Churn dataset in sql but getting 0

In SQL I am checking for Null Value. There is presence of Null values in the data set but I am still getting 0 in the output. I have uploaded the data twice to check if the updation is correct or not, but getting same result. In Python Null values are showing but in SQL I am facing issue. Can someone help me what is the potential problem. I am using the following code.

SELECT 'Tenure' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE Tenure IS NULL
UNION
SELECT 'WarehouseToHome' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE warehousetohome IS NULL
UNION
SELECT 'HourSpendonApp' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE hourspendonapp IS NULL
UNION
SELECT 'OrderAmountHikeFromLastYear' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE orderamounthikefromlastyear IS NULL 
UNION
SELECT 'CouponUsed' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE couponused IS NULL 
UNION
SELECT 'OrderCount' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE ordercount IS NULL 
UNION
SELECT 'DaySinceLastOrder' as ColumnName, COUNT(*) AS NullCount 
FROM ecomm.eccomchurn
WHERE daysincelastorder IS NULL;
Tags:

2

Answers


  1. 0

    It is possible that the NULL values in your dataset are not actually NULL, but instead contain empty strings or other placeholders. Try modifying your code to check for empty string values as well by using the following syntax:

    WHERE Tenure IS NULL OR Tenure = ''

    This will catch any instances where the value is an empty string rather than a true NULL value. Repeat this modification for each of the columns you are checking and see if it resolves the issue with your output showing 0 counts for NULL values.

    Login or Signup to reply.
  2. 0

    This should be a comment but its a bit big for the wee box.

    There is presence of Null values in the data set

    How did you determine that? I suspect @Benyamin might be correct.

    Your query probably won’t performance very well, consider:

    SELECT SUM(IFNULL(Tenure), 1, IF(''=Tenure, 1, 0))
,      SUM(IFNULL(warehousetohome), 1, IF(''=warehousetohome, 1, 0))
,      SUM(IFNULL(hourspendonapp), 1, IF(''=hourspendonapp, 1, 0))
[...]
,      SUM(IFNULL(DaySinceLastOrder), 1, IF(''=DaySinceLastOrder, 1, 0))
FROM ecomm.eccomchurn;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search