I need a count of NULL from 2 tables that are joined in MySQL. Sample data like this:
datefield FROM TABLE calendar (contain dates from start to end of this year)
-----------
TABLE value (data stored)
+------------+-------+
| date | keter |
+------------+-------+
| 2021-08-01 | 11 |
| 2021-08-04 | 0 |
| 2021-08-07 | 20 |
| 2021-08-08 | 15 |
| 2021-08-11 | 0 |
+------------+-------+
I am using the following query to combine and display data from calendar and value tables.
SELECT datefield,keter FROM calendar
LEFT JOIN kehadiran ON datefield=tgl AND id_kar IN ('110101')
WHERE datefield BETWEEN '2021-08-01' AND '2021-08-15' GROUP BY datefield;
result :
+------------+-------+
| datefield | keter |
+------------+-------+
| 2021-08-01 | 11 |
| 2021-08-02 | NULL |
| 2021-08-03 | NULL |
| 2021-08-04 | 0 |
| 2021-08-05 | NULL |
| 2021-08-06 | NULL |
| 2021-08-07 | 20 |
| 2021-08-08 | 15 |
| 2021-08-09 | NULL |
| 2021-08-10 | NULL |
| 2021-08-11 | 0 |
| 2021-08-12 | NULL |
| 2021-08-13 | NULL |
| 2021-08-14 | NULL |
| 2021-08-15 | NULL |
+------------+-------+
I use query based on this question (3 table join counting nulls), I didn't get the result I wanted. The query is this :
SELECT SUM(k.keter) FROM kehadiran k
LEFT OUTER JOIN calendar c ON c.datefield = k.keter AND id_kar IN ('110101')
WHERE datefield BETWEEN '2021-08-01' AND '2021-08-12' AND k.keter is NULL;
result:
+--------------+
| SUM(k.keter) |
+--------------+
| NULL |
+--------------+
the result i wanted :
+--------------+
| SUM(k.keter) |
+--------------+
| 10 |
+--------------+
How should I count NULL from the joined table as mentioned above?
You swapped the tables in your last query which is incorrect. Use the query that worked and use COUNT(*) with WHERE right_table.any_notnull_column IS NULL:
SELECT COUNT(*)
FROM calendar
LEFT JOIN kehadiran k ON datefield=tgl AND id_kar IN ('110101')
WHERE datefield BETWEEN '2021-08-01' AND '2021-08-15'
AND k.keter is NULL