How can I count pairs when the columns are equal/not equal in TSQL
2
I have a dataset that I want to count when the pairs are equal and not equal, grouping by one column. A toy dataset would look like this:
DECLARE @t Table (
SampleNumber varchar(max),
SampleType varchar(max),
A varchar(max),
B varchar(max))
INSERT INTO @t VALUES
('B1','DD','PASS','FAIL'),
('B1','DS','PASS','FAIL'),
('B2','DD','PASS','PASS'),
('B2','DS','PASS','PASS'),
('B3','DD','NA','NA'),
('B3','DS','NA','PASS'),
('B4','DD','PASS','PASS'),
('B4','DS','PASS','FAIL')
SELECT * FROM @t
So for this dataset I would like the output to look something like this:
I will note that I have about many SampleNumbers (100+) and about 10 columns (e.g. A through J) that I need to roll the data up from so I was hoping to a flexible solution.
It would be horribly inefficient to split the SampleType into two temp tables (e.g. DD and DS) and join by SampleNumber.
How would I accomplish this?
sql-server tsql count asked Nov 13 '18 at 15:58
user918967user918967
84541435
add a comment |
2
I have a dataset that I want to count when the pairs are equal and not equal, grouping by one column. A toy dataset would look like this:
DECLARE @t Table (
SampleNumber varchar(max),
SampleType varchar(max),
A varchar(max),
B varchar(max))
INSERT INTO @t VALUES
('B1','DD','PASS','FAIL'),
('B1','DS','PASS','FAIL'),
('B2','DD','PASS','PASS'),
('B2','DS','PASS','PASS'),
('B3','DD','NA','NA'),
('B3','DS','NA','PASS'),
('B4','DD','PASS','PASS'),
('B4','DS','PASS','FAIL')
SELECT * FROM @t
So for this dataset I would like the output to look something like this:
I will note that I have about many SampleNumbers (100+) and about 10 columns (e.g. A through J) that I need to roll the data up from so I was hoping to a flexible solution.
It would be horribly inefficient to split the SampleType into two temp tables (e.g. DD and DS) and join by SampleNumber.
How would I accomplish this?
sql-server tsql count asked Nov 13 '18 at 15:58
user918967user918967
84541435
1
Have you tried using aCASEexpression inside yourCOUNT/SUM?
– Larnu
Nov 13 '18 at 16:00
Also, should'NA','PASSnot be counted in both the Pass and NA rows in Disagree, or is the grouping based solely on the value ofA?
– Larnu
Nov 13 '18 at 16:02
Its just if the pairs (DD/DS) are equal (e.g. if B1 DD = B1 DS ). So B4 DD-DS in column A is equal (PASS=PASS) but B4 DD-DS in column B is not equal (PASS != FAIL)
– user918967
Nov 13 '18 at 16:11
add a comment |
2
2
2
I have a dataset that I want to count when the pairs are equal and not equal, grouping by one column. A toy dataset would look like this:
DECLARE @t Table (
SampleNumber varchar(max),
SampleType varchar(max),
A varchar(max),
B varchar(max))
INSERT INTO @t VALUES
('B1','DD','PASS','FAIL'),
('B1','DS','PASS','FAIL'),
('B2','DD','PASS','PASS'),
('B2','DS','PASS','PASS'),
('B3','DD','NA','NA'),
('B3','DS','NA','PASS'),
('B4','DD','PASS','PASS'),
('B4','DS','PASS','FAIL')
SELECT * FROM @t
So for this dataset I would like the output to look something like this:
I will note that I have about many SampleNumbers (100+) and about 10 columns (e.g. A through J) that I need to roll the data up from so I was hoping to a flexible solution.
It would be horribly inefficient to split the SampleType into two temp tables (e.g. DD and DS) and join by SampleNumber.
How would I accomplish this?
sql-server tsql count asked Nov 13 '18 at 15:58
user918967user918967
84541435
I have a dataset that I want to count when the pairs are equal and not equal, grouping by one column. A toy dataset would look like this:
DECLARE @t Table (
SampleNumber varchar(max),
SampleType varchar(max),
A varchar(max),
B varchar(max))
INSERT INTO @t VALUES
('B1','DD','PASS','FAIL'),
('B1','DS','PASS','FAIL'),
('B2','DD','PASS','PASS'),
('B2','DS','PASS','PASS'),
('B3','DD','NA','NA'),
('B3','DS','NA','PASS'),
('B4','DD','PASS','PASS'),
('B4','DS','PASS','FAIL')
SELECT * FROM @t
So for this dataset I would like the output to look something like this:
I will note that I have about many SampleNumbers (100+) and about 10 columns (e.g. A through J) that I need to roll the data up from so I was hoping to a flexible solution.
It would be horribly inefficient to split the SampleType into two temp tables (e.g. DD and DS) and join by SampleNumber.
How would I accomplish this?
sql-server tsql count sql-server tsql count asked Nov 13 '18 at 15:58
user918967user918967
84541435
asked Nov 13 '18 at 15:58
user918967user918967
84541435
edited Nov 13 '18 at 18:30
user918967
asked Nov 13 '18 at 15:58
user918967user918967
84541435
asked Nov 13 '18 at 15:58
user918967user918967
84541435
asked Nov 13 '18 at 15:58
user918967user918967
84541435
84541435
1
Have you tried using aCASEexpression inside yourCOUNT/SUM?
– Larnu
Nov 13 '18 at 16:00
Also, should'NA','PASSnot be counted in both the Pass and NA rows in Disagree, or is the grouping based solely on the value ofA?
– Larnu
Nov 13 '18 at 16:02
Its just if the pairs (DD/DS) are equal (e.g. if B1 DD = B1 DS ). So B4 DD-DS in column A is equal (PASS=PASS) but B4 DD-DS in column B is not equal (PASS != FAIL)
– user918967
Nov 13 '18 at 16:11
add a comment |
1
Have you tried using aCASEexpression inside yourCOUNT/SUM?
– Larnu
Nov 13 '18 at 16:00
Also, should'NA','PASSnot be counted in both the Pass and NA rows in Disagree, or is the grouping based solely on the value ofA?
– Larnu
Nov 13 '18 at 16:02
Its just if the pairs (DD/DS) are equal (e.g. if B1 DD = B1 DS ). So B4 DD-DS in column A is equal (PASS=PASS) but B4 DD-DS in column B is not equal (PASS != FAIL)
– user918967
Nov 13 '18 at 16:11
1
1
Have you tried using a
CASE expression inside your COUNT/SUM?– Larnu
Nov 13 '18 at 16:00
Have you tried using a
CASE expression inside your COUNT/SUM?– Larnu
Nov 13 '18 at 16:00
Also, should
'NA','PASS not be counted in both the Pass and NA rows in Disagree, or is the grouping based solely on the value of A?– Larnu
Nov 13 '18 at 16:02
Also, should
'NA','PASS not be counted in both the Pass and NA rows in Disagree, or is the grouping based solely on the value of A?– Larnu
Nov 13 '18 at 16:02
Its just if the pairs (DD/DS) are equal (e.g. if B1 DD = B1 DS ). So B4 DD-DS in column A is equal (PASS=PASS) but B4 DD-DS in column B is not equal (PASS != FAIL)
– user918967
Nov 13 '18 at 16:11
Its just if the pairs (DD/DS) are equal (e.g. if B1 DD = B1 DS ). So B4 DD-DS in column A is equal (PASS=PASS) but B4 DD-DS in column B is not equal (PASS != FAIL)
– user918967
Nov 13 '18 at 16:11
add a comment |
1 Answer
1
active
oldest
votes
0
SELECT IQ.A, SUM(IQ.X) as DD_AGREE, SUM(1 -IQ.X) AS DD_DIsagree
FROM
(SELECT A,CASE WHEN A = B THEN 1 ELSE 0 END X FROM @t) IQ
GROUP BY IQ.A
UNION SELECT B, 0,0 FROM @T where B NOT IN(SELECT t2.A FROM @t t2)
answered Nov 13 '18 at 17:22
CatoCato
2,892210
I am thinking this gives the correct answer but for the wrong reason? It should not check Column A vs Column B but instead Row1 Column A vs Row2 Column A, Row3 Column A vs Row4 Column A and so on.
– user918967
Nov 13 '18 at 18:24
I will change the data to show what I mean by making Row1 and Row2 of Column B both be Fail and they should agree.
– user918967
Nov 13 '18 at 18:28
in that case you will also need a row_number, or some sort of specified order, at the moment it isn't clear how the order of rows is determined
– Cato
Nov 14 '18 at 10:21
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53284843%2fhow-can-i-count-pairs-when-the-columns-are-equal-not-equal-in-tsql%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
0
SELECT IQ.A, SUM(IQ.X) as DD_AGREE, SUM(1 -IQ.X) AS DD_DIsagree
FROM
(SELECT A,CASE WHEN A = B THEN 1 ELSE 0 END X FROM @t) IQ
GROUP BY IQ.A
UNION SELECT B, 0,0 FROM @T where B NOT IN(SELECT t2.A FROM @t t2)
answered Nov 13 '18 at 17:22
CatoCato
2,892210
I am thinking this gives the correct answer but for the wrong reason? It should not check Column A vs Column B but instead Row1 Column A vs Row2 Column A, Row3 Column A vs Row4 Column A and so on.
– user918967
Nov 13 '18 at 18:24
I will change the data to show what I mean by making Row1 and Row2 of Column B both be Fail and they should agree.
– user918967
Nov 13 '18 at 18:28
in that case you will also need a row_number, or some sort of specified order, at the moment it isn't clear how the order of rows is determined
– Cato
Nov 14 '18 at 10:21
add a comment |
0
SELECT IQ.A, SUM(IQ.X) as DD_AGREE, SUM(1 -IQ.X) AS DD_DIsagree
FROM
(SELECT A,CASE WHEN A = B THEN 1 ELSE 0 END X FROM @t) IQ
GROUP BY IQ.A
UNION SELECT B, 0,0 FROM @T where B NOT IN(SELECT t2.A FROM @t t2)
answered Nov 13 '18 at 17:22
CatoCato
2,892210
I am thinking this gives the correct answer but for the wrong reason? It should not check Column A vs Column B but instead Row1 Column A vs Row2 Column A, Row3 Column A vs Row4 Column A and so on.
– user918967
Nov 13 '18 at 18:24
I will change the data to show what I mean by making Row1 and Row2 of Column B both be Fail and they should agree.
– user918967
Nov 13 '18 at 18:28
in that case you will also need a row_number, or some sort of specified order, at the moment it isn't clear how the order of rows is determined
– Cato
Nov 14 '18 at 10:21
add a comment |
0
0
0
SELECT IQ.A, SUM(IQ.X) as DD_AGREE, SUM(1 -IQ.X) AS DD_DIsagree
FROM
(SELECT A,CASE WHEN A = B THEN 1 ELSE 0 END X FROM @t) IQ
GROUP BY IQ.A
UNION SELECT B, 0,0 FROM @T where B NOT IN(SELECT t2.A FROM @t t2)
answered Nov 13 '18 at 17:22
CatoCato
2,892210
SELECT IQ.A, SUM(IQ.X) as DD_AGREE, SUM(1 -IQ.X) AS DD_DIsagree
FROM
(SELECT A,CASE WHEN A = B THEN 1 ELSE 0 END X FROM @t) IQ
GROUP BY IQ.A
UNION SELECT B, 0,0 FROM @T where B NOT IN(SELECT t2.A FROM @t t2)
answered Nov 13 '18 at 17:22
CatoCato
2,892210
answered Nov 13 '18 at 17:22
CatoCato
2,892210
answered Nov 13 '18 at 17:22
CatoCato
2,892210
answered Nov 13 '18 at 17:22
CatoCato
2,892210
2,892210
I am thinking this gives the correct answer but for the wrong reason? It should not check Column A vs Column B but instead Row1 Column A vs Row2 Column A, Row3 Column A vs Row4 Column A and so on.
– user918967
Nov 13 '18 at 18:24
I will change the data to show what I mean by making Row1 and Row2 of Column B both be Fail and they should agree.
– user918967
Nov 13 '18 at 18:28
in that case you will also need a row_number, or some sort of specified order, at the moment it isn't clear how the order of rows is determined
– Cato
Nov 14 '18 at 10:21
add a comment |
I am thinking this gives the correct answer but for the wrong reason? It should not check Column A vs Column B but instead Row1 Column A vs Row2 Column A, Row3 Column A vs Row4 Column A and so on.
– user918967
Nov 13 '18 at 18:24
I will change the data to show what I mean by making Row1 and Row2 of Column B both be Fail and they should agree.
– user918967
Nov 13 '18 at 18:28
in that case you will also need a row_number, or some sort of specified order, at the moment it isn't clear how the order of rows is determined
– Cato
Nov 14 '18 at 10:21
I am thinking this gives the correct answer but for the wrong reason? It should not check Column A vs Column B but instead Row1 Column A vs Row2 Column A, Row3 Column A vs Row4 Column A and so on.
– user918967
Nov 13 '18 at 18:24
I am thinking this gives the correct answer but for the wrong reason? It should not check Column A vs Column B but instead Row1 Column A vs Row2 Column A, Row3 Column A vs Row4 Column A and so on.
– user918967
Nov 13 '18 at 18:24
I will change the data to show what I mean by making Row1 and Row2 of Column B both be Fail and they should agree.
– user918967
Nov 13 '18 at 18:28
I will change the data to show what I mean by making Row1 and Row2 of Column B both be Fail and they should agree.
– user918967
Nov 13 '18 at 18:28
in that case you will also need a row_number, or some sort of specified order, at the moment it isn't clear how the order of rows is determined
– Cato
Nov 14 '18 at 10:21
in that case you will also need a row_number, or some sort of specified order, at the moment it isn't clear how the order of rows is determined
– Cato
Nov 14 '18 at 10:21
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53284843%2fhow-can-i-count-pairs-when-the-columns-are-equal-not-equal-in-tsql%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
1
Have you tried using a
CASEexpression inside yourCOUNT/SUM?– Larnu
Nov 13 '18 at 16:00
Also, should
'NA','PASSnot be counted in both the Pass and NA rows in Disagree, or is the grouping based solely on the value ofA?– Larnu
Nov 13 '18 at 16:02
Its just if the pairs (DD/DS) are equal (e.g. if B1 DD = B1 DS ). So B4 DD-DS in column A is equal (PASS=PASS) but B4 DD-DS in column B is not equal (PASS != FAIL)
– user918967
Nov 13 '18 at 16:11