mysql - count null and non-null fields in one query

Question

Input table (named ruchin)

id     comment           order_id          deleted_at
1        abc               1234              NULL
2        abcd              1234              TimeStamp
3        xyz               1234              NULL
4        pqr               1234              TimeStamp
5        as                4567               NULL
6        lo                4567               NULL

and I want output as

order_id        count(deleted_at==Timestamp)    count(deleted_at==NULL)
1234                   2                                 2
4567                   0                                  2

and I want this to be in single query.How can I do this???

Derlin · Accepted Answer · 2018-06-09 06:18:06Z

3

You can use the function count() for that, since it doesn't count NULL values.

Here is the query:

SELECT order_id, 
       count(deleted_at) as 'del is not null',
       count(order_id) - count(deleted_at) as 'del is NULL' 
FROM ruchin 
GROUP BY order_id

answered Jun 9, 2018 at 6:18

Derlin

9,9112 gold badges34 silver badges57 bronze badges

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

1 Comment

RUCHIN CHAUHAN Over a year ago

Here NULL is the parameter,it doesn't means actual NULL,consider NULL as variable x.

Collectives™ on Stack Overflow

mysql - count null and non-null fields in one query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related