Select where every row on a given date is null mysql

[Drew106]

I need to have a query that select where every row for a certain column on a given date is null.

 

I've been trying queries similar to this with no success:

SELECT COUNT(column1) AS tot, DATE_FORMAT(date, '%Y-%m-%d') AS dt FROM lists WHERE column1 IS NULL GROUP BY dt;

[bepai]

I'm new at sql but wouldnt select column1 where datecolumn =Null

 

if you want the date column there to edit then  select column1,datecolumn where datecolumn =Null

 

 

Null is not a string or a int so I dont know if you have to enclose this in "" or ''

[DavidAM]

I'm pretty sure that COUNT ignores NULLs, so in your original query, try counting the primary key or some other column that is sure to have a value.

 

SELECT COUNT(ID) AS tot, DATE_FORMAT(date, '%Y-%m-%d') AS dt 
FROM lists WHERE column1 IS NULL GROUP BY dt;

 

 

@bepai: You cannot use 'equals' with NULL. You have to use IS NULL.

[Drew106]

When I run that query, all dates that have all rows as NULL come up as 0 since it's counting the columns with a value. I need it to spit out those 0s and exclude everything that has a value greater than 0.  I tried using WHERE column1 < 1 but that obviously doesn't work.

[DavidAM]

I'm not sure I understand. You want to count all rows where column1 is NULL or is equal to zero? If so, you can choose one of these:

SELECT COUNT(ID) AS tot, DATE_FORMAT(date, '%Y-%m-%d') AS dt 
FROM lists WHERE column1 IS NULL OR column1 = 0 GROUP BY dt;

SELECT COUNT(ID) AS tot, DATE_FORMAT(date, '%Y-%m-%d') AS dt 
FROM lists WHERE IFNULL(column1, 0) = 0 GROUP BY dt;

 

The first one just checks for either condition. If you are running against a large table, and column1 is indexed, this will probably have better performance than the second one.

 

The second one just considers a null value to be zero and checks if the value is zero. You should get the same results from both queries, it's just a matter of performance and preference.

 

If I missed the boat here, then try explaining a little more and maybe show some sample data.

[Drew106]

tot, date

 

0, 2011-03-11

9, 2011-03-14

4, 2011-04-04

0, 2011-04-05

 

That's a sample of what I get running both my original attempts at the query and your suggested one.  The query I'm trying to get would include 2011-03-11 and 2011-04-05 where tot is 0 but would not show 2011-03-14 or 2011-04-04 where the totals are 9 and 4.

[DavidAM]

If you are wanting rows where the COUNT is zero, then you need to use a HAVING clause

 

SELECT COUNT(column1) AS tot, DATE_FORMAT(date, '%Y-%m-%d') AS dt 
FROM lists 
WHERE column1 IS NULL 
GROUP BY dt
HAVING COUNT(column1) = 0; 

[Drew106]

.