Extract rows of a table, that include less than x NULLs [duplicate]
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
This question already has an answer here:
Count where any 3 columns have values (not null)
1 answer
I am working with a SQL Server database, which includes a lot of NULLs.
To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2).
My database is similar to this structure:
c1 c2 c3 c4 c5
-----------------------------------------------------
2 3 NULL 1 2
2 NULL NULL 1 2
2 3 NULL NULL 2
NULL 3 NULL 1 NULL
2 3 NULL 1 2
I tried the query, which doesn't return an error, but no rows are selected:
SELECT * FROM test123
WHERE ((ISNULL(c1,1) + ISNULL(c2,1) + ISNULL(c3,1) + ISNULL(c4,1) + ISNULL(c5,1)) < 2);
I expect this query to return the 1st and the fifth row, but the result contains 0 rows.
I can't test the following code, because I don't have the rights to write on the database, but here is a (pseudo-) code for creating a table like mine:
CREATE TABLE test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
) GO
INSERT test123(c1,c2,c3,c4,c5)
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
sql-server query isnull
marked as duplicate by Paul White♦
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Apr 5 at 4:58
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
Count where any 3 columns have values (not null)
1 answer
I am working with a SQL Server database, which includes a lot of NULLs.
To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2).
My database is similar to this structure:
c1 c2 c3 c4 c5
-----------------------------------------------------
2 3 NULL 1 2
2 NULL NULL 1 2
2 3 NULL NULL 2
NULL 3 NULL 1 NULL
2 3 NULL 1 2
I tried the query, which doesn't return an error, but no rows are selected:
SELECT * FROM test123
WHERE ((ISNULL(c1,1) + ISNULL(c2,1) + ISNULL(c3,1) + ISNULL(c4,1) + ISNULL(c5,1)) < 2);
I expect this query to return the 1st and the fifth row, but the result contains 0 rows.
I can't test the following code, because I don't have the rights to write on the database, but here is a (pseudo-) code for creating a table like mine:
CREATE TABLE test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
) GO
INSERT test123(c1,c2,c3,c4,c5)
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
sql-server query isnull
marked as duplicate by Paul White♦
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Apr 5 at 4:58
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
Count where any 3 columns have values (not null)
1 answer
I am working with a SQL Server database, which includes a lot of NULLs.
To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2).
My database is similar to this structure:
c1 c2 c3 c4 c5
-----------------------------------------------------
2 3 NULL 1 2
2 NULL NULL 1 2
2 3 NULL NULL 2
NULL 3 NULL 1 NULL
2 3 NULL 1 2
I tried the query, which doesn't return an error, but no rows are selected:
SELECT * FROM test123
WHERE ((ISNULL(c1,1) + ISNULL(c2,1) + ISNULL(c3,1) + ISNULL(c4,1) + ISNULL(c5,1)) < 2);
I expect this query to return the 1st and the fifth row, but the result contains 0 rows.
I can't test the following code, because I don't have the rights to write on the database, but here is a (pseudo-) code for creating a table like mine:
CREATE TABLE test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
) GO
INSERT test123(c1,c2,c3,c4,c5)
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
sql-server query isnull
This question already has an answer here:
Count where any 3 columns have values (not null)
1 answer
I am working with a SQL Server database, which includes a lot of NULLs.
To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2).
My database is similar to this structure:
c1 c2 c3 c4 c5
-----------------------------------------------------
2 3 NULL 1 2
2 NULL NULL 1 2
2 3 NULL NULL 2
NULL 3 NULL 1 NULL
2 3 NULL 1 2
I tried the query, which doesn't return an error, but no rows are selected:
SELECT * FROM test123
WHERE ((ISNULL(c1,1) + ISNULL(c2,1) + ISNULL(c3,1) + ISNULL(c4,1) + ISNULL(c5,1)) < 2);
I expect this query to return the 1st and the fifth row, but the result contains 0 rows.
I can't test the following code, because I don't have the rights to write on the database, but here is a (pseudo-) code for creating a table like mine:
CREATE TABLE test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
) GO
INSERT test123(c1,c2,c3,c4,c5)
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
This question already has an answer here:
Count where any 3 columns have values (not null)
1 answer
sql-server query isnull
sql-server query isnull
edited Apr 3 at 19:23
MDCCL
6,86331745
6,86331745
asked Apr 3 at 16:57
sqlNewiesqlNewie
283
283
marked as duplicate by Paul White♦
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Apr 5 at 4:58
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Paul White♦
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Apr 5 at 4:58
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You should use a case statement like this:
SELECT *
FROM test123
WHERE (
(CASE WHEN C1 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C2 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C3 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C4 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C5 IS NULL THEN 1 ELSE 0 END)
< 2);
The ISNULL
approach is returning your actual values when the value isn't NULL
, which pushes all of the rows over the 2 mark.
add a comment |
Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table:
CREATE TABLE #test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
);
INSERT #test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
To see why ISNULL
isn't effective here, run this query:
SELECT ISNULL(c1,1), ISNULL(c2,1), ISNULL(c3,1), ISNULL(c4,1), ISNULL(c5,1)
FROM #test123;
You've given every column in every row a value. So now you're evaluating the SUM of inflated values, and erroneously evaluating a property of the actual value (what happens when one of the values is negative?), instead of evaluating the COUNT of values that either are NULL or are NOT NULL.
It's more code but a simple way to address this is:
SELECT * FROM #test123
WHERE CASE WHEN c1 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c2 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c3 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c4 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c5 IS NULL THEN 1 ELSE 0 END < 2;
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You should use a case statement like this:
SELECT *
FROM test123
WHERE (
(CASE WHEN C1 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C2 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C3 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C4 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C5 IS NULL THEN 1 ELSE 0 END)
< 2);
The ISNULL
approach is returning your actual values when the value isn't NULL
, which pushes all of the rows over the 2 mark.
add a comment |
You should use a case statement like this:
SELECT *
FROM test123
WHERE (
(CASE WHEN C1 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C2 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C3 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C4 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C5 IS NULL THEN 1 ELSE 0 END)
< 2);
The ISNULL
approach is returning your actual values when the value isn't NULL
, which pushes all of the rows over the 2 mark.
add a comment |
You should use a case statement like this:
SELECT *
FROM test123
WHERE (
(CASE WHEN C1 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C2 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C3 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C4 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C5 IS NULL THEN 1 ELSE 0 END)
< 2);
The ISNULL
approach is returning your actual values when the value isn't NULL
, which pushes all of the rows over the 2 mark.
You should use a case statement like this:
SELECT *
FROM test123
WHERE (
(CASE WHEN C1 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C2 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C3 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C4 IS NULL THEN 1 ELSE 0 END +
CASE WHEN C5 IS NULL THEN 1 ELSE 0 END)
< 2);
The ISNULL
approach is returning your actual values when the value isn't NULL
, which pushes all of the rows over the 2 mark.
answered Apr 3 at 17:07
Josh DarnellJosh Darnell
8,05522243
8,05522243
add a comment |
add a comment |
Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table:
CREATE TABLE #test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
);
INSERT #test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
To see why ISNULL
isn't effective here, run this query:
SELECT ISNULL(c1,1), ISNULL(c2,1), ISNULL(c3,1), ISNULL(c4,1), ISNULL(c5,1)
FROM #test123;
You've given every column in every row a value. So now you're evaluating the SUM of inflated values, and erroneously evaluating a property of the actual value (what happens when one of the values is negative?), instead of evaluating the COUNT of values that either are NULL or are NOT NULL.
It's more code but a simple way to address this is:
SELECT * FROM #test123
WHERE CASE WHEN c1 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c2 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c3 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c4 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c5 IS NULL THEN 1 ELSE 0 END < 2;
add a comment |
Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table:
CREATE TABLE #test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
);
INSERT #test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
To see why ISNULL
isn't effective here, run this query:
SELECT ISNULL(c1,1), ISNULL(c2,1), ISNULL(c3,1), ISNULL(c4,1), ISNULL(c5,1)
FROM #test123;
You've given every column in every row a value. So now you're evaluating the SUM of inflated values, and erroneously evaluating a property of the actual value (what happens when one of the values is negative?), instead of evaluating the COUNT of values that either are NULL or are NOT NULL.
It's more code but a simple way to address this is:
SELECT * FROM #test123
WHERE CASE WHEN c1 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c2 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c3 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c4 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c5 IS NULL THEN 1 ELSE 0 END < 2;
add a comment |
Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table:
CREATE TABLE #test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
);
INSERT #test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
To see why ISNULL
isn't effective here, run this query:
SELECT ISNULL(c1,1), ISNULL(c2,1), ISNULL(c3,1), ISNULL(c4,1), ISNULL(c5,1)
FROM #test123;
You've given every column in every row a value. So now you're evaluating the SUM of inflated values, and erroneously evaluating a property of the actual value (what happens when one of the values is negative?), instead of evaluating the COUNT of values that either are NULL or are NOT NULL.
It's more code but a simple way to address this is:
SELECT * FROM #test123
WHERE CASE WHEN c1 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c2 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c3 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c4 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c5 IS NULL THEN 1 ELSE 0 END < 2;
Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table:
CREATE TABLE #test123(
c1 float,
c2 float,
c3 float,
c4 float,
c5 float
);
INSERT #test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
To see why ISNULL
isn't effective here, run this query:
SELECT ISNULL(c1,1), ISNULL(c2,1), ISNULL(c3,1), ISNULL(c4,1), ISNULL(c5,1)
FROM #test123;
You've given every column in every row a value. So now you're evaluating the SUM of inflated values, and erroneously evaluating a property of the actual value (what happens when one of the values is negative?), instead of evaluating the COUNT of values that either are NULL or are NOT NULL.
It's more code but a simple way to address this is:
SELECT * FROM #test123
WHERE CASE WHEN c1 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c2 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c3 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c4 IS NULL THEN 1 ELSE 0 END
+ CASE WHEN c5 IS NULL THEN 1 ELSE 0 END < 2;
answered Apr 3 at 17:09
Aaron Bertrand♦Aaron Bertrand
154k18298493
154k18298493
add a comment |
add a comment |