How to eleiminate NULL with 0 in sql

Question

I have a simple table now I want when I run sql statement it should replace NULL with 0 using mysql

Here is what I have tried so far

SELECT IFNULL(targetbuttonname, 0), sessions.sid, events.datetime, count(*) as num_rows,
  count(distinct sessions.sid) as sessions, 
  sum( targetbuttonname = 'kredyt' ) as num_kredyt, 
  sum(devicetype ='Computer') as num_computer from events

Now when I run my script it still returns null

Here is DEMO demo

What is wrong with my query statement?

Possible duplicate of Replacing NULL with 0 in a SQL server query — Tayyab
– Tayyab, Commented Nov 20, 2019 at 13:59

VBoka · Accepted Answer · 2019-11-20 14:06:49Z

1

Use COALESCE

SELECT coalesce(NULL,0), sessions.sid, events.datetime, count(*) as num_rows, count(distinct sessions.sid) as sessions, 
  sum( targetbuttonname = 'kredyt' ) as num_kredyt, 
  sum( targetbuttonname = 'konto' ) as num_konto,
  sum( targetbuttonname = 'czat' ) as num_czat,
  sum( targetbuttonname = 'video-voice_btns' ) as num_voice,
  sum( targetbuttonname = 'video-close_btn' )  as num_close,
  sum( targetbuttonname = 'video-muted_btn' ) as num_muted,
  sum( targetbuttonname = 'video-play_btn' )  as num_play,
  sum( targetbuttonname = 'video-pause_btn' )  as num_pause,
  sum( targetbuttonname = 'video-replay_btn' ) as num_replay, 
  sum(watchtime) as num_watchtime, 
  sum(devicetype ='Computer') as num_computer from events  INNER JOIN sessions ON (events.sid =sessions.sid) WHERE events.datetime BETWEEN '2019-11-11' AND '2019-11-21'

DEMO: http://sqlfiddle.com/#!9/3cf3cf/10

IFNULL will also work:

SELECT ifnull(targetbuttonname,0), sessions.sid, events.datetime, count(*) as num_rows, count(distinct sessions.sid) as sessions, 
  sum( targetbuttonname = 'kredyt' ) as num_kredyt, 
  sum( targetbuttonname = 'konto' ) as num_konto,
  sum( targetbuttonname = 'czat' ) as num_czat,
  sum( targetbuttonname = 'video-voice_btns' ) as num_voice,
  sum( targetbuttonname = 'video-close_btn' )  as num_close,
  sum( targetbuttonname = 'video-muted_btn' ) as num_muted,
  sum( targetbuttonname = 'video-play_btn' )  as num_play,
  sum( targetbuttonname = 'video-pause_btn' )  as num_pause,
  sum( targetbuttonname = 'video-replay_btn' ) as num_replay, 
  sum(watchtime) as num_watchtime, 
  sum(devicetype ='Computer') as num_computer from events  INNER JOIN sessions ON (events.sid =sessions.sid) WHERE events.datetime BETWEEN '2019-11-11' AND '2019-11-21'

Here is the DEMO: http://sqlfiddle.com/#!9/3cf3cf/14

edited Nov 20, 2019 at 14:06

answered Nov 20, 2019 at 14:01

VBoka

9,0933 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

The Dead Man Over a year ago

Thanks for help , now when I change the date range to dates which are not in the table still return NAN, ,meaning null

The Dead Man Over a year ago

meaning on console it shows null and on browser it shows NAN

The Dead Man Over a year ago

just change the range of the dates like this WHERE events.datetime BETWEEN '2019-04-11' AND '2019-03-21'

VBoka Over a year ago

Ok, but then you have to do coalesce for every column!

VBoka Over a year ago

So, this is what you want: sqlfiddle.com/#!9/332ef1/7 ??

|

Collectives™ on Stack Overflow

How to eleiminate NULL with 0 in sql

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related