November 13, 2013 at 5:56 am
Hi all,
I am facing a problem in writing a query.
Here is my requirement
i have a <products> table with columns <productid> <productname> <manufactureDate> <DeliveryDate>
and some columns are filled with null values
i am trying to find the number of null columns with a counter.
the execution flow has to be like whenever i come across a null the counter has to be incremented by 1.
kindly help me in writing this query.
Regards
--------------
Trainee SQL
November 13, 2013 at 6:19 am
Something like
select
NullValues =
case when col1 is null then 1 else 0 end
+case when col2 is null then 1 else 0 end
+case when col3 is null then 1 else 0 end
November 13, 2013 at 6:43 am
Trainee SQL (11/13/2013)
Hi all,I am facing a problem in writing a query.
Here is my requirement
i have a <products> table with columns <productid> <productname> <manufactureDate> <DeliveryDate>
and some columns are filled with null values
i am trying to find the number of null columns with a counter.
the execution flow has to be like whenever i come across a null the counter has to be incremented by 1.
kindly help me in writing this query.
Regards
--------------
Trainee SQL
I created the DDL and populated some same data for you so the results are easily reproducible.
create table product (
productid integer,
productname integer,
manufacturedate datetime,
deliverydate datetime);
insert into product
values(1, 0, null, null),
(2, 1, null, null),
(null, 2, 1, null),
(null, 3, 2, 1),
(null, 4, null, null);
If you want the number of nulls in each column, try this.
select sum(case when productid is null then 1 else 0 end) null_productid,
sum(case when productname is null then 1 else 0 end) null_productname,
sum(case when manufacturedate is null then 1 else 0 end) null_manufacturedate,
sum(case when deliverydate is null then 1 else 0 end) null_deliverydate
from product;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply