Count where any 3 columns have values (not null)Finding empty columns of a table in PostgreSQLMySQL IS NULL /...
How to draw the figure with four pentagons?
Why are electrically insulating heatsinks so rare? Is it just cost?
Today is the Center
What do you call someone who asks many questions?
Why can't we play rap on piano?
What exploit are these user agents trying to use?
Blender 2.8 I can't see vertices, edges or faces in edit mode
What about the virus in 12 Monkeys?
How can saying a song's name be a copyright violation?
SSH "lag" in LAN on some machines, mixed distros
Does a druid starting with a bow start with no arrows?
Is there a hemisphere-neutral way of specifying a season?
90's TV series where a boy goes to another dimension through portal near power lines
Assassin's bullet with mercury
Combinations of multiple lists
Would Slavery Reparations be considered Bills of Attainder and hence Illegal?
Twin primes whose sum is a cube
Why is consensus so controversial in Britain?
How to prevent "they're falling in love" trope
Can a virus destroy the BIOS of a modern computer?
How do I write bicross product symbols in latex?
Has there ever been an airliner design involving reducing generator load by installing solar panels?
Is it unprofessional to ask if a job posting on GlassDoor is real?
Watching something be written to a file live with tail
Count where any 3 columns have values (not null)
Finding empty columns of a table in PostgreSQLMySQL IS NULL / IS NOT NULL Misbehaving?Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQLFind tables where all columns in all rows are nullIn what case is a count(x or null) needed in Gaps and Islands?MySQL: SELECT with SUM() and COUNT() as new columnsHow to select TOP 1 value from each column where value is not nullWhy query plans is different for queries with the same WHERE Clause?Sorting a Date Column in Microsoft SQL Server Management StudioPostgres - How to get Multi-Count Query using a Where Clause for a Join Table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 columns are filled (Not Null). Can we do this through a query?
In the example shown, the query should return count as 4 rows.
sql-server count null
edited 3 mins ago
Paul White♦
54.1k14287460
asked Dec 7 '17 at 10:34
ShivaShiva
4072514
add a comment |
I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 columns are filled (Not Null). Can we do this through a query?
In the example shown, the query should return count as 4 rows.
sql-server count null
edited 3 mins ago
Paul White♦
54.1k14287460
asked Dec 7 '17 at 10:34
ShivaShiva
4072514
add a comment |
I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 columns are filled (Not Null). Can we do this through a query?
In the example shown, the query should return count as 4 rows.
sql-server count null
edited 3 mins ago
Paul White♦
54.1k14287460
asked Dec 7 '17 at 10:34
ShivaShiva
4072514
I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 columns are filled (Not Null). Can we do this through a query?
In the example shown, the query should return count as 4 rows.
sql-server count null
sql-server count null
edited 3 mins ago
Paul White♦
54.1k14287460
asked Dec 7 '17 at 10:34
ShivaShiva
4072514
edited 3 mins ago
Paul White♦
54.1k14287460
asked Dec 7 '17 at 10:34
ShivaShiva
4072514
edited 3 mins ago
Paul White♦
54.1k14287460
edited 3 mins ago
Paul White♦
54.1k14287460
edited 3 mins ago
Paul White♦
54.1k14287460
54.1k14287460
asked Dec 7 '17 at 10:34
ShivaShiva
4072514
asked Dec 7 '17 at 10:34
ShivaShiva
4072514
asked Dec 7 '17 at 10:34
ShivaShiva
4072514
4072514
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can mark any NULL as 0 and not NULL as 1 and calculate the sum,
it will give you the number of not NULL values in a row.
If you want to count only the rows where there are exactly 3 not NULL values use this code (you should write a sum of cases for all 15 columns, in my example they are only 6):
declare @ItemProperties table (col1 int, col2 int, col3 int, col4 int, col5 int, col6 int);
insert into @ItemProperties
values
(1, 1, 1, null, null, 1),
(1, null, null, null, null, 1),
(null, 1, 1, 1, null, null),
(null, null, 1, null, null, 1),
(null, 1, 1, 1, 1, 1);
with cte as
(
select *,
case when col1 is null then 0 else 1 end +
case when col2 is null then 0 else 1 end +
case when col3 is null then 0 else 1 end +
case when col4 is null then 0 else 1 end +
case when col5 is null then 0 else 1 end +
case when col6 is null then 0 else 1 end as Num_of_not_NULL_columns
from @ItemProperties
)
--select *
--from cte
--where Num_of_not_NULL_columns = 3
select count(*) as cnt
from cte
where Num_of_not_NULL_columns = 3;
If instead you want to count the rows with at least 3 not NULL values change the condition as where Num_of_not_NULL_columns >= 3;
answered Dec 7 '17 at 10:53
sepupicsepupic
7,831820
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f192610%2fcount-where-any-3-columns-have-values-not-null%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can mark any NULL as 0 and not NULL as 1 and calculate the sum,
it will give you the number of not NULL values in a row.
If you want to count only the rows where there are exactly 3 not NULL values use this code (you should write a sum of cases for all 15 columns, in my example they are only 6):
declare @ItemProperties table (col1 int, col2 int, col3 int, col4 int, col5 int, col6 int);
insert into @ItemProperties
values
(1, 1, 1, null, null, 1),
(1, null, null, null, null, 1),
(null, 1, 1, 1, null, null),
(null, null, 1, null, null, 1),
(null, 1, 1, 1, 1, 1);
with cte as
(
select *,
case when col1 is null then 0 else 1 end +
case when col2 is null then 0 else 1 end +
case when col3 is null then 0 else 1 end +
case when col4 is null then 0 else 1 end +
case when col5 is null then 0 else 1 end +
case when col6 is null then 0 else 1 end as Num_of_not_NULL_columns
from @ItemProperties
)
--select *
--from cte
--where Num_of_not_NULL_columns = 3
select count(*) as cnt
from cte
where Num_of_not_NULL_columns = 3;
If instead you want to count the rows with at least 3 not NULL values change the condition as where Num_of_not_NULL_columns >= 3;
answered Dec 7 '17 at 10:53
sepupicsepupic
7,831820
add a comment |
You can mark any NULL as 0 and not NULL as 1 and calculate the sum,
it will give you the number of not NULL values in a row.
If you want to count only the rows where there are exactly 3 not NULL values use this code (you should write a sum of cases for all 15 columns, in my example they are only 6):
declare @ItemProperties table (col1 int, col2 int, col3 int, col4 int, col5 int, col6 int);
insert into @ItemProperties
values
(1, 1, 1, null, null, 1),
(1, null, null, null, null, 1),
(null, 1, 1, 1, null, null),
(null, null, 1, null, null, 1),
(null, 1, 1, 1, 1, 1);
with cte as
(
select *,
case when col1 is null then 0 else 1 end +
case when col2 is null then 0 else 1 end +
case when col3 is null then 0 else 1 end +
case when col4 is null then 0 else 1 end +
case when col5 is null then 0 else 1 end +
case when col6 is null then 0 else 1 end as Num_of_not_NULL_columns
from @ItemProperties
)
--select *
--from cte
--where Num_of_not_NULL_columns = 3
select count(*) as cnt
from cte
where Num_of_not_NULL_columns = 3;
If instead you want to count the rows with at least 3 not NULL values change the condition as where Num_of_not_NULL_columns >= 3;
answered Dec 7 '17 at 10:53
sepupicsepupic
7,831820
add a comment |
You can mark any NULL as 0 and not NULL as 1 and calculate the sum,
it will give you the number of not NULL values in a row.
If you want to count only the rows where there are exactly 3 not NULL values use this code (you should write a sum of cases for all 15 columns, in my example they are only 6):
declare @ItemProperties table (col1 int, col2 int, col3 int, col4 int, col5 int, col6 int);
insert into @ItemProperties
values
(1, 1, 1, null, null, 1),
(1, null, null, null, null, 1),
(null, 1, 1, 1, null, null),
(null, null, 1, null, null, 1),
(null, 1, 1, 1, 1, 1);
with cte as
(
select *,
case when col1 is null then 0 else 1 end +
case when col2 is null then 0 else 1 end +
case when col3 is null then 0 else 1 end +
case when col4 is null then 0 else 1 end +
case when col5 is null then 0 else 1 end +
case when col6 is null then 0 else 1 end as Num_of_not_NULL_columns
from @ItemProperties
)
--select *
--from cte
--where Num_of_not_NULL_columns = 3
select count(*) as cnt
from cte
where Num_of_not_NULL_columns = 3;
If instead you want to count the rows with at least 3 not NULL values change the condition as where Num_of_not_NULL_columns >= 3;
answered Dec 7 '17 at 10:53
sepupicsepupic
7,831820
You can mark any NULL as 0 and not NULL as 1 and calculate the sum,
it will give you the number of not NULL values in a row.
If you want to count only the rows where there are exactly 3 not NULL values use this code (you should write a sum of cases for all 15 columns, in my example they are only 6):
declare @ItemProperties table (col1 int, col2 int, col3 int, col4 int, col5 int, col6 int);
insert into @ItemProperties
values
(1, 1, 1, null, null, 1),
(1, null, null, null, null, 1),
(null, 1, 1, 1, null, null),
(null, null, 1, null, null, 1),
(null, 1, 1, 1, 1, 1);
with cte as
(
select *,
case when col1 is null then 0 else 1 end +
case when col2 is null then 0 else 1 end +
case when col3 is null then 0 else 1 end +
case when col4 is null then 0 else 1 end +
case when col5 is null then 0 else 1 end +
case when col6 is null then 0 else 1 end as Num_of_not_NULL_columns
from @ItemProperties
)
--select *
--from cte
--where Num_of_not_NULL_columns = 3
select count(*) as cnt
from cte
where Num_of_not_NULL_columns = 3;
If instead you want to count the rows with at least 3 not NULL values change the condition as where Num_of_not_NULL_columns >= 3;
answered Dec 7 '17 at 10:53
sepupicsepupic
7,831820
answered Dec 7 '17 at 10:53
sepupicsepupic
7,831820
answered Dec 7 '17 at 10:53
sepupicsepupic
7,831820
answered Dec 7 '17 at 10:53
sepupicsepupic
7,831820
7,831820
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f192610%2fcount-where-any-3-columns-have-values-not-null%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
