SK2017
Reputation: 773
IFNULL() Still returning Null - MYSQL
I am writing a query that groups together some dates working out counts of of rows.. I am using a left join to join the two tables together and were rows in the second column are NULL I want to output 0.
Here is a snippet of my data.
TRANSACTIONS TABLE
ITEM DOE T_DOEDAY
42 11/06/2015 10:40 11/06/2015
14 12/06/2015 06:19 12/06/2015
2151 15/06/2015 07:27 15/06/2015
2114 16/06/2015 09:58 16/06/2015
98541 17/06/2015 07:15 17/06/2015
101 18/06/2015 10:14 18/06/2015
9854 19/06/2015 06:14 19/06/2015
2121 22/06/2015 07:14 22/06/2015
2354 23/06/2015 07:12 23/06/2015
252 24/06/2015 07:12 24/06/2015
5654 15/06/2015 10:12 25/06/2015
245 15/06/2015 09:11 26/06/2015
..............
ATTRIBUTES TABLE
ITEM DOE DOEDAY
CD 11/06/2015 10:40 11/06/2015
CD 12/06/2015 06:19 12/06/2015
OP 15/06/2015 07:27 15/06/2015
DF 16/06/2015 09:58 16/06/2015
CD 19/06/2015 06:14 19/06/2015
CD 22/06/2015 07:14 22/06/2015
OD 23/06/2015 07:12 23/06/2015
OD 24/06/2015 07:12 24/06/2015
OD 15/06/2015 10:12 25/06/2015
CD 15/06/2015 09:11 26/06/2015
...............
I have tried the following in my query -
SELECT *
FROM (SELECT COUNT(DISTINCT T.ITEM) AS ITEM_COUNT, T.ITEM, T.DOE, T.DOEDAY AS T_DOEDAY, T.pos
FROM TRANSACTIONS T
group by T_DOEDAY ) J1
LEFT JOIN
(SELECT IFNULL(COUNT(T1.DEFECT), 0) as DEFECT_COUNT,
T1.DOEDAY AS T1_DOEDAY
FROM ATTRIBUTES T1
GROUP BY T1_DOEDAY ) J2
ON T_DOEDAY = T1_DOEDAY
Which outputs the following -
ITEM_COUNT ITEM DOE T_DOEDAY DEFECT_COUNT T1_DOEDAY
32 10 11/06/2015 10:40 11/06/2015 0 11/06/2015
69 18 12/06/2015 06:19 12/06/2015 0 12/06/2015
49 2 15/06/2015 07:27 15/06/2015 0 15/06/2015
14 21 16/06/2015 09:58 16/06/2015 0 16/06/2015
3 6 17/06/2015 07:14 17/06/2015 NULL NULL
16 18 18/06/2015 09:18 18/06/2015 NULL NULL
31 22 19/06/2015 06:14 19/06/2015 0 19/06/2015
98 89 22/06/2015 07:14 22/06/2015 0 22/06/2015
14 204 23/06/2015 07:12 23/06/2015 0 23/06/2015
68 202 24/06/2015 07:12 24/06/2015 0 24/06/2015
26 8 15/06/2015 10:12 25/06/2015 0 25/06/2015
133 1 15/06/2015 09:11 26/06/2015 0 26/06/2015
So i tried this query-
SELECT *
FROM (SELECT COUNT(DISTINCT T.ITEM) AS ITEM_COUNT, T.ITEM, T.DOE, T.DOEDAY AS T_DOEDAY, T.pos
FROM TRANSACTIONS T group by T_DOEDAY ) J1
LEFT JOIN
(SELECT IF(COUNT(T1.DEFECT) = NULL,0,COUNT(T1.DEFECT)) as DEFECT_COUNT,
T1.DOEDAY AS T1_DOEDAY
FROM ATTRIBUTES T1
GROUP BY T1_DOEDAY ) J2
ON T_DOEDAY = T1_DOEDAY
Which outputs the following again -
ITEM_COUNT ITEM DOE T_DOEDAY DEFECT_COUNT T1_DOEDAY
32 10 11/06/2015 10:40 11/06/2015 0 11/06/2015
69 18 12/06/2015 06:19 12/06/2015 0 12/06/2015
49 2 15/06/2015 07:27 15/06/2015 0 15/06/2015
14 21 16/06/2015 09:58 16/06/2015 0 16/06/2015
3 6 17/06/2015 07:14 17/06/2015 NULL NULL
16 18 18/06/2015 09:18 18/06/2015 NULL NULL
31 22 19/06/2015 06:14 19/06/2015 0 19/06/2015
98 89 22/06/2015 07:14 22/06/2015 0 22/06/2015
14 204 23/06/2015 07:12 23/06/2015 0 23/06/2015
68 202 24/06/2015 07:12 24/06/2015 0 24/06/2015
26 8 15/06/2015 10:12 25/06/2015 0 25/06/2015
133 1 15/06/2015 09:11 26/06/2015 0 26/06/2015
Expected Result
ITEM_COUNT ITEM DOE T_DOEDAY DEFECT_COUNT T1_DOEDAY
32 10 11/06/2015 10:40 11/06/2015 0 11/06/2015
69 18 12/06/2015 06:19 12/06/2015 0 12/06/2015
49 2 15/06/2015 07:27 15/06/2015 0 15/06/2015
14 21 16/06/2015 09:58 16/06/2015 0 16/06/2015
3 6 17/06/2015 07:14 17/06/2015 0 NULL
16 18 18/06/2015 09:18 18/06/2015 0 NULL
31 22 19/06/2015 06:14 19/06/2015 0 19/06/2015
98 89 22/06/2015 07:14 22/06/2015 0 22/06/2015
14 204 23/06/2015 07:12 23/06/2015 0 23/06/2015
68 202 24/06/2015 07:12 24/06/2015 0 24/06/2015
26 8 15/06/2015 10:12 25/06/2015 0 25/06/2015
133 1 15/06/2015 09:11 26/06/2015 0 26/06/2015
I don't understand why the IFNULL or IF functions aren't working as they should, the sql runs fine, no errors are flagged up..
If anyone could shed some light on this I would greatly appreciate it.
Upvotes: 0
Views: 1178
Answers (3)
Barmar
Reputation: 781013
You need to do the IFNULL after joining, not in the subquery that does the counting. The nulls are added by the LEFT JOIN when there are no matching rows.
SELECT J1.*, IFNULL(defect_count, 0) AS defect_count
FROM (SELECT COUNT(DISTINCT T.ITEM) AS ITEM_COUNT, T.ITEM, T.DOE, T.DOEDAY AS T_DOEDAY, T.pos
FROM TRANSACTIONS T
group by T_DOEDAY ) J1
LEFT JOIN
(SELECT COUNT(*) as DEFECT_COUNT,
T1.DOEDAY AS T1_DOEDAY
FROM ATTRIBUTES T1
GROUP BY T1_DOEDAY ) J2
ON J1.T_DOEDAY = J2.T1_DOEDAY
Upvotes: 1
Alec.
Reputation: 5525
I don't see you using IFNULL try the query below.
SELECT * FROM (SELECT COUNT(DISTINCT T.ITEM) AS ITEM_COUNT, T.ITEM, T.DOE, T.DOEDAY AS T_DOEDAY, T.pos FROM TRANSACTIONS T group by T_DOEDAY ) J1 LEFT JOIN
(SELECT IFNULL(COUNT(T1.DEFECT),0) as DEFECT_COUNT,
T1.DOEDAY AS T1_DOEDAY FROM ATTRIBUTES T1 GROUP BY T1_DOEDAY ) J2 ON T_DOEDAY = T1_DOE
Upvotes: 0
Jens
Reputation: 69440
you have to add a default value if isnull is true
SELECT * FROM (SELECT COUNT(DISTINCT T.ITEM) AS ITEM_COUNT, T.ITEM, T.DOE, T.DOEDAY AS T_DOEDAY, T.pos FROM TRANSACTIONS T group by T_DOEDAY ) J1 LEFT JOIN
(SELECT IFNULL(COUNT(T1.DEFECT),0) as DEFECT_COUNT,
T1.DOEDAY AS T1_DOEDAY FROM ATTRIBUTES T1 GROUP BY T1_DOEDAY ) J2 ON T_DOEDAY = T1_DOE
Upvotes: 0
Related Questions
- MySQL IFNULL is not returning result
- SQL Query is not returning NULL values despite using IFNULL (MySQL)
- MySQL IFNULL still returns NULL
- Why my IfNull() function doesn't work?
- COALESCE and IFNULL not working as expected
- IFNULL is not working
- MySQL IFNULL not returning 0 on table with no matching records as expected
- IFNULL not working like I want to
- Why is IFNULL() not working here
- IFNull in MySQL