CODEWITHSUNDEEP
mariadbmariasql
Malinka Jayasinghe
Malinka Jayasinghe

Reputation: 7

Replace NULL with the Date in the WHERE clause in Mysql

So, in order to get 0s in my count column I have tried out this query which works.

SELECT b.TXT_RECORD_DATE, a.TXT_CALL_TYPE, 
      SUM(CASE 
        WHEN b.TXT_CALL_TYPE IS NOT NULL
          THEN 1
        ELSE 0
      END) AS StatusCount
FROM (
  SELECT DISTINCT TXT_CALL_TYPE
  FROM CDR
  ) a
LEFT JOIN CDR b ON a.TXT_CALL_TYPE = b.TXT_CALL_TYPE AND b.TXT_RECORD_DATE IN ('2022-04-12', '2022-04-13','2022-04-14')
GROUP BY a.TXT_CALL_TYPE, b.TXT_RECORD_DATE;

But it shows [NULL]s in the columns where StatusCount = 0

So my question is that is there a way to actually assign the date that is being currently searched instead of the [NULL]

The current result looks like this

TXT_RECORD_DATE TXT_CALL_TYPE StatusCount
BRD 0
2022-04-12 Busy Call 9
IDIN 0
IDOT 0
2022-04-12 Incoming - Missed Call 133
2022-04-13 Incoming - Missed Call 38
2022-04-14 Incoming - Missed Call 29
ITRS 0
IVIN 0
2022-04-12 IVOT 21
2022-04-13 IVOT 27
2022-04-14 IVOT 20
PIN 0
2022-04-12 POT 1
2022-04-12 PTRS 19
2022-04-13 PTRS 4
2022-04-14 PTRS 14

Sorry if I forgot anything or was not clear. I'm writing to you in the middle of the night and is so tired. Thanks Anyways. You guys are always awesome.

Upvotes: 0

Views: 155

Answers (1)

Nick
Nick

Reputation: 147216

You need to generate a list of the dates you are interested in and CROSS JOIN that to the list of call types; then you can LEFT JOIN that to the call records to get the result you want. In MariaDB you can make use of the sequence storage engine to easily generate a list of the dates:

SELECT d.TXT_RECORD_DATE, a.TXT_CALL_TYPE, 
      SUM(CASE 
        WHEN b.TXT_CALL_TYPE IS NOT NULL
          THEN 1
        ELSE 0
      END) AS StatusCount
FROM (
  SELECT '2022-04-12' + INTERVAL (seq) DAY AS TXT_RECORD_DATE
  FROM seq_0_to_2
) d
CROSS JOIN (
  SELECT DISTINCT TXT_CALL_TYPE
  FROM CDR
  WHERE TXT_CALL_TYPE IS NOT NULL
) a
LEFT JOIN CDR b ON a.TXT_CALL_TYPE = b.TXT_CALL_TYPE AND d.TXT_RECORD_DATE = b.TXT_RECORD_DATE
GROUP BY d.TXT_RECORD_DATE, a.TXT_CALL_TYPE

Demo on dbfiddle

Upvotes: 0

Related Questions