Question 20244 in Mysql Question posted in
The official MySQL documentation can be found here.

How to calculate null among other data in sql – Mysql

I want to count how much NULL data gets to the row with the contents of "resuss"

Like that:

open_date m+ id flag time hibernate
202203 202205 ax1 resuss 1
202203 202209 ax1 resuss 3
202203 202206 ay2 resuss 2
202203 202209 ay2 null 3
202203 202209 az3 null 6
202203 202209 av4 resuss 5

DataTable is:

open_date m+ id flag
202203 202204 ax1 null
202203 202205 ax1 resuss
202203 202206 ax1 null
202203 202207 ax1 null
202203 202208 ax1 null
202203 202209 ax1 resuss
202203 202204 ay2 null
202203 202205 ay2 null
202203 202206 ay2 resuss
202203 202207 ay2 null
202203 202208 ay2 null
202203 202209 ay2 null
202203 202204 az3 null
202203 202205 az3 null
202203 202206 az3 null
202203 202207 az3 null
202203 202208 az3 null
202203 202209 az3 null
202203 202204 av4 null
202203 202205 av4 null
202203 202206 av4 null
202203 202207 av4 null
202203 202208 av4 null
202203 202209 av4 resuss

My script SQL like that, but this is false:

SELECT
    opendate, m+, id, sum(case when flag is null than 1 else 0 end) as timeHibernate
FROM dataTable
WHERE flag = "resuss"
GROUP BY opendate, m+, id

This false, because column of *timeHibernate* count 0

Please help me

Tags:

2

Answers


  1. 0

    You can simply adding "flag" into group by and also replace sum() into count(*)
    I think it may return your expected result.

    SELECT
opendate, m+, id,flag , Count(*) as timeHibernate
FROM dataTable
WHERE flag = "resuss"
GROUP BY opendate, m+, id,flag

    Otherwise, I think you can also try replacing

    case when flag is null then 1 else 0 end

    into

    case when flag = '' then 1 else 0 end

    or

    case when flag = 'null' then 1 else 0 end

    According to the given information,
    It may because the flag ‘Null’ may not really a null values.
    That cause the (case when) return a diffrent result intead of your expected result.
    If it still not working you can also test in a smaller scale or more simple statment like 

    select * from dataTable
where flag = ''flag = 'null'flag is null

    to test out the how to filtering the null values

    Good Luck!

    Login or Signup to reply.
  2. 0 
    SELECT
    open_date, m+, id, SUM(CASE WHEN flag IS NULL THEN 1 ELSE 0 END) AS timeHibernate
FROM dataTable
WHERE flag = "resuss" OR flag IS NULL
GROUP BY open_date, m+, id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search