Mixing
How to group the rows and get the count based on other column values in oracle
0
I have a table (with name 'jobs') having columns 'ID' and 'Type'. ID column can have same id more than once and the type column with have two values say 'A' and 'B'. I want to get the count of all the ids with type just 'A', with type just 'B' and with type 'A' and 'B' both.
I have tried the below query but it gives the count of type 'A' and type 'B'.
SELECT distinct
type,
COUNT( 1 ) OVER ( PARTITION BY type) AS Count
FROM jobs
Sample Data:
ID Type
1 A
1 B
2 A
2 B
3 A
4 A
5 A
6 B
7 B
8 B
9 B
Expected Output is:
Count of ID’s having just A as type: 3
Count of ID’s having just B as type : 4
Count of ID’s having A and B as type : 2
sql oracle oracle11g
edited Jan 2 at 11:59
Gordon Linoff
790k35314418
asked Jan 2 at 10:50
palpraspalpras
105
add a comment |
0
I have a table (with name 'jobs') having columns 'ID' and 'Type'. ID column can have same id more than once and the type column with have two values say 'A' and 'B'. I want to get the count of all the ids with type just 'A', with type just 'B' and with type 'A' and 'B' both.
I have tried the below query but it gives the count of type 'A' and type 'B'.
SELECT distinct
type,
COUNT( 1 ) OVER ( PARTITION BY type) AS Count
FROM jobs
Sample Data:
ID Type
1 A
1 B
2 A
2 B
3 A
4 A
5 A
6 B
7 B
8 B
9 B
Expected Output is:
Count of ID’s having just A as type: 3
Count of ID’s having just B as type : 4
Count of ID’s having A and B as type : 2
sql oracle oracle11g
edited Jan 2 at 11:59
Gordon Linoff
790k35314418
asked Jan 2 at 10:50
palpraspalpras
105
Your question is confusing. Please add expected output.
– Tim Biegeleisen
Jan 2 at 10:59
@TimBiegeleisen Output is simple as below: Count of Type A : 10 Count of Type B: 15 Count of type A and B : 5 Getting count of type 'A' and 'B' is simple enough, I am confused how to get the count of the ids in table having records for both type 'A' and type 'B' separately
– palpras
Jan 2 at 11:01
Your query is almost correct for the first 2 cases, I have to think about 3rd scenario A+B : SELECT distinct type, COUNT( DISTINCT ID ) OVER ( PARTITION BY type) AS Count FROM jobs
– Ted at ORCL.Pro
Jan 2 at 12:46
add a comment |
0
0
0
I have a table (with name 'jobs') having columns 'ID' and 'Type'. ID column can have same id more than once and the type column with have two values say 'A' and 'B'. I want to get the count of all the ids with type just 'A', with type just 'B' and with type 'A' and 'B' both.
I have tried the below query but it gives the count of type 'A' and type 'B'.
SELECT distinct
type,
COUNT( 1 ) OVER ( PARTITION BY type) AS Count
FROM jobs
Sample Data:
ID Type
1 A
1 B
2 A
2 B
3 A
4 A
5 A
6 B
7 B
8 B
9 B
Expected Output is:
Count of ID’s having just A as type: 3
Count of ID’s having just B as type : 4
Count of ID’s having A and B as type : 2
sql oracle oracle11g
edited Jan 2 at 11:59
Gordon Linoff
790k35314418
asked Jan 2 at 10:50
palpraspalpras
105
I have a table (with name 'jobs') having columns 'ID' and 'Type'. ID column can have same id more than once and the type column with have two values say 'A' and 'B'. I want to get the count of all the ids with type just 'A', with type just 'B' and with type 'A' and 'B' both.
I have tried the below query but it gives the count of type 'A' and type 'B'.
SELECT distinct
type,
COUNT( 1 ) OVER ( PARTITION BY type) AS Count
FROM jobs
Sample Data:
ID Type
1 A
1 B
2 A
2 B
3 A
4 A
5 A
6 B
7 B
8 B
9 B
Expected Output is:
Count of ID’s having just A as type: 3
Count of ID’s having just B as type : 4
Count of ID’s having A and B as type : 2
sql oracle oracle11g
sql oracle oracle11g
edited Jan 2 at 11:59
Gordon Linoff
790k35314418
asked Jan 2 at 10:50
palpraspalpras
105
edited Jan 2 at 11:59
Gordon Linoff
790k35314418
asked Jan 2 at 10:50
palpraspalpras
105
edited Jan 2 at 11:59
Gordon Linoff
790k35314418
edited Jan 2 at 11:59
Gordon Linoff
790k35314418
edited Jan 2 at 11:59
Gordon Linoff
790k35314418
790k35314418
asked Jan 2 at 10:50
palpraspalpras
105
asked Jan 2 at 10:50
palpraspalpras
105
asked Jan 2 at 10:50
palpraspalpras
105
105
Your question is confusing. Please add expected output.
– Tim Biegeleisen
Jan 2 at 10:59
@TimBiegeleisen Output is simple as below: Count of Type A : 10 Count of Type B: 15 Count of type A and B : 5 Getting count of type 'A' and 'B' is simple enough, I am confused how to get the count of the ids in table having records for both type 'A' and type 'B' separately
– palpras
Jan 2 at 11:01
Your query is almost correct for the first 2 cases, I have to think about 3rd scenario A+B : SELECT distinct type, COUNT( DISTINCT ID ) OVER ( PARTITION BY type) AS Count FROM jobs
– Ted at ORCL.Pro
Jan 2 at 12:46
add a comment |
Your question is confusing. Please add expected output.
– Tim Biegeleisen
Jan 2 at 10:59
@TimBiegeleisen Output is simple as below: Count of Type A : 10 Count of Type B: 15 Count of type A and B : 5 Getting count of type 'A' and 'B' is simple enough, I am confused how to get the count of the ids in table having records for both type 'A' and type 'B' separately
– palpras
Jan 2 at 11:01
Your query is almost correct for the first 2 cases, I have to think about 3rd scenario A+B : SELECT distinct type, COUNT( DISTINCT ID ) OVER ( PARTITION BY type) AS Count FROM jobs
– Ted at ORCL.Pro
Jan 2 at 12:46
Your question is confusing. Please add expected output.
– Tim Biegeleisen
Jan 2 at 10:59
Your question is confusing. Please add expected output.
– Tim Biegeleisen
Jan 2 at 10:59
@TimBiegeleisen Output is simple as below: Count of Type A : 10 Count of Type B: 15 Count of type A and B : 5 Getting count of type 'A' and 'B' is simple enough, I am confused how to get the count of the ids in table having records for both type 'A' and type 'B' separately
– palpras
Jan 2 at 11:01
@TimBiegeleisen Output is simple as below: Count of Type A : 10 Count of Type B: 15 Count of type A and B : 5 Getting count of type 'A' and 'B' is simple enough, I am confused how to get the count of the ids in table having records for both type 'A' and type 'B' separately
– palpras
Jan 2 at 11:01
Your query is almost correct for the first 2 cases, I have to think about 3rd scenario A+B : SELECT distinct type, COUNT( DISTINCT ID ) OVER ( PARTITION BY type) AS Count FROM jobs
– Ted at ORCL.Pro
Jan 2 at 12:46
Your query is almost correct for the first 2 cases, I have to think about 3rd scenario A+B : SELECT distinct type, COUNT( DISTINCT ID ) OVER ( PARTITION BY type) AS Count FROM jobs
– Ted at ORCL.Pro
Jan 2 at 12:46
add a comment |
4 Answers
4
active
oldest
votes
0
You may try this.
--test data
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
) --test data ends
select count(only_a) as "Count of Type A Only",
count(only_b) as "Count of Type B Only",
count(both_a_and_b) as "Count of Type A and B both"
FROM
(
SELECT
ID,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'A' THEN 1 END only_a,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'B' THEN 1 END only_b,
CASE WHEN COUNT(DISTINCT TYPE) = 2 THEN 1 END both_a_and_b
FROM jobs
WHERE type in ('A','B')
GROUP BY ID
)s;
Demo
answered Jan 2 at 11:20
Kaushik NayakKaushik Nayak
20.8k41332
@ Kaushik Nayak : A & B are the values of the field 'Type'. I just need to aggregate the values based of ids. Please check my expected output.
– palpras
Jan 2 at 11:26
@palpras : Have you tried the query on your tables? What output did you get?
– Kaushik Nayak
Jan 2 at 12:03
@ Kaushik Nayak . Thanks, I am able to get the required output :)
– palpras
Jan 3 at 12:11
@ Kaushik Nayak : Is it feasible to get the same result without using the group by, just thinking from the perspective of making it more performance efficient
– palpras
Jan 4 at 9:19
@palpras : There may be, but I don't think they would improve the performance any better andgroup byis the most sensible thing to go for when you have multiple ids to aggregate. One option to speed up the query would be to add an index onid,type. You may ask a separate question if you face performance issues giving the details of theexplain planetc as performance purely depends on your data
– Kaushik Nayak
Jan 4 at 11:02
add a comment |
0
I would write this as:
select sum(case when num_As > 0 and num_Bs = 0 then 1 else 0 end) as num_A_only,
sum(case when num_As = 0 and num_Bs > 0 then 1 else 0 end) as num_B_only,
sum(case when num_As > 0 and num_Bs > 0 then 1 else 0 end) as num_A_and_B
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t;
If you want this in separate rows:
select (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end) as grp,
count(*)
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t
group by (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end);
answered Jan 2 at 12:01
Gordon LinoffGordon Linoff
790k35314418
Thanks, your answer also works :)
– palpras
Jan 3 at 12:12
add a comment |
0
Based on your sample data the below will work as expected:
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
)
SELECT distinct
j1.type||j2.type as type,
COUNT( DISTINCT j1.ID ) OVER ( PARTITION BY j1.type||j2.type) AS Count
FROM jobs j1 inner join jobs j2 on j1.id = j2.id and j1.type <= j2.type
answered Jan 2 at 12:54
Ted at ORCL.ProTed at ORCL.Pro
1,35028
add a comment |
0
Get 3 different counters for each case:
select
(select count(distinct id) from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
) countera,
(select count(distinct id) from jobs j
where ((select min(jobs.type) from jobs where jobs.id = j.id) = 'b')
) counterb,
(select count(distinct id) from jobs j
where (
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
)) counterab
from dual;
See the demo
To get 1 row for each counter:
select
'Count of IDs having just A as type :' descr,
count(distinct id) counter from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
union all
select
'Count of IDs having just B as type :' descr,
count(distinct id) from jobs j
where ((select min(jobs.type) counter from jobs where jobs.id = j.id) = 'b')
union all
select
'Count of IDs having A and B as type :' descr,
count(distinct id) counter from jobs j
where
(
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
);
See the demo
answered Jan 2 at 13:16
forpasforpas
17.8k3728
I am not getting the expected results from your query
– palpras
Jan 4 at 9:20
@palpras there are 2 demos where you see the expected results.
– forpas
Jan 4 at 9:34
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%2f54004994%2fhow-to-group-the-rows-and-get-the-count-based-on-other-column-values-in-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You may try this.
--test data
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
) --test data ends
select count(only_a) as "Count of Type A Only",
count(only_b) as "Count of Type B Only",
count(both_a_and_b) as "Count of Type A and B both"
FROM
(
SELECT
ID,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'A' THEN 1 END only_a,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'B' THEN 1 END only_b,
CASE WHEN COUNT(DISTINCT TYPE) = 2 THEN 1 END both_a_and_b
FROM jobs
WHERE type in ('A','B')
GROUP BY ID
)s;
Demo
answered Jan 2 at 11:20
Kaushik NayakKaushik Nayak
20.8k41332
@ Kaushik Nayak : A & B are the values of the field 'Type'. I just need to aggregate the values based of ids. Please check my expected output.
– palpras
Jan 2 at 11:26
@palpras : Have you tried the query on your tables? What output did you get?
– Kaushik Nayak
Jan 2 at 12:03
@ Kaushik Nayak . Thanks, I am able to get the required output :)
– palpras
Jan 3 at 12:11
@ Kaushik Nayak : Is it feasible to get the same result without using the group by, just thinking from the perspective of making it more performance efficient
– palpras
Jan 4 at 9:19
@palpras : There may be, but I don't think they would improve the performance any better andgroup byis the most sensible thing to go for when you have multiple ids to aggregate. One option to speed up the query would be to add an index onid,type. You may ask a separate question if you face performance issues giving the details of theexplain planetc as performance purely depends on your data
– Kaushik Nayak
Jan 4 at 11:02
add a comment |
0
You may try this.
--test data
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
) --test data ends
select count(only_a) as "Count of Type A Only",
count(only_b) as "Count of Type B Only",
count(both_a_and_b) as "Count of Type A and B both"
FROM
(
SELECT
ID,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'A' THEN 1 END only_a,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'B' THEN 1 END only_b,
CASE WHEN COUNT(DISTINCT TYPE) = 2 THEN 1 END both_a_and_b
FROM jobs
WHERE type in ('A','B')
GROUP BY ID
)s;
Demo
answered Jan 2 at 11:20
Kaushik NayakKaushik Nayak
20.8k41332
@ Kaushik Nayak : A & B are the values of the field 'Type'. I just need to aggregate the values based of ids. Please check my expected output.
– palpras
Jan 2 at 11:26
@palpras : Have you tried the query on your tables? What output did you get?
– Kaushik Nayak
Jan 2 at 12:03
@ Kaushik Nayak . Thanks, I am able to get the required output :)
– palpras
Jan 3 at 12:11
@ Kaushik Nayak : Is it feasible to get the same result without using the group by, just thinking from the perspective of making it more performance efficient
– palpras
Jan 4 at 9:19
@palpras : There may be, but I don't think they would improve the performance any better andgroup byis the most sensible thing to go for when you have multiple ids to aggregate. One option to speed up the query would be to add an index onid,type. You may ask a separate question if you face performance issues giving the details of theexplain planetc as performance purely depends on your data
– Kaushik Nayak
Jan 4 at 11:02
add a comment |
0
0
0
You may try this.
--test data
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
) --test data ends
select count(only_a) as "Count of Type A Only",
count(only_b) as "Count of Type B Only",
count(both_a_and_b) as "Count of Type A and B both"
FROM
(
SELECT
ID,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'A' THEN 1 END only_a,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'B' THEN 1 END only_b,
CASE WHEN COUNT(DISTINCT TYPE) = 2 THEN 1 END both_a_and_b
FROM jobs
WHERE type in ('A','B')
GROUP BY ID
)s;
Demo
answered Jan 2 at 11:20
Kaushik NayakKaushik Nayak
20.8k41332
You may try this.
--test data
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
) --test data ends
select count(only_a) as "Count of Type A Only",
count(only_b) as "Count of Type B Only",
count(both_a_and_b) as "Count of Type A and B both"
FROM
(
SELECT
ID,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'A' THEN 1 END only_a,
CASE WHEN MAX(TYPE) = MIN(TYPE) and MIN(TYPE) = 'B' THEN 1 END only_b,
CASE WHEN COUNT(DISTINCT TYPE) = 2 THEN 1 END both_a_and_b
FROM jobs
WHERE type in ('A','B')
GROUP BY ID
)s;
Demo
answered Jan 2 at 11:20
Kaushik NayakKaushik Nayak
20.8k41332
answered Jan 2 at 11:20
Kaushik NayakKaushik Nayak
20.8k41332
answered Jan 2 at 11:20
Kaushik NayakKaushik Nayak
20.8k41332
answered Jan 2 at 11:20
Kaushik NayakKaushik Nayak
20.8k41332
20.8k41332
@ Kaushik Nayak : A & B are the values of the field 'Type'. I just need to aggregate the values based of ids. Please check my expected output.
– palpras
Jan 2 at 11:26
@palpras : Have you tried the query on your tables? What output did you get?
– Kaushik Nayak
Jan 2 at 12:03
@ Kaushik Nayak . Thanks, I am able to get the required output :)
– palpras
Jan 3 at 12:11
@ Kaushik Nayak : Is it feasible to get the same result without using the group by, just thinking from the perspective of making it more performance efficient
– palpras
Jan 4 at 9:19
@palpras : There may be, but I don't think they would improve the performance any better andgroup byis the most sensible thing to go for when you have multiple ids to aggregate. One option to speed up the query would be to add an index onid,type. You may ask a separate question if you face performance issues giving the details of theexplain planetc as performance purely depends on your data
– Kaushik Nayak
Jan 4 at 11:02
add a comment |
@ Kaushik Nayak : A & B are the values of the field 'Type'. I just need to aggregate the values based of ids. Please check my expected output.
– palpras
Jan 2 at 11:26
@palpras : Have you tried the query on your tables? What output did you get?
– Kaushik Nayak
Jan 2 at 12:03
@ Kaushik Nayak . Thanks, I am able to get the required output :)
– palpras
Jan 3 at 12:11
@ Kaushik Nayak : Is it feasible to get the same result without using the group by, just thinking from the perspective of making it more performance efficient
– palpras
Jan 4 at 9:19
@palpras : There may be, but I don't think they would improve the performance any better andgroup byis the most sensible thing to go for when you have multiple ids to aggregate. One option to speed up the query would be to add an index onid,type. You may ask a separate question if you face performance issues giving the details of theexplain planetc as performance purely depends on your data
– Kaushik Nayak
Jan 4 at 11:02
@ Kaushik Nayak : A & B are the values of the field 'Type'. I just need to aggregate the values based of ids. Please check my expected output.
– palpras
Jan 2 at 11:26
@ Kaushik Nayak : A & B are the values of the field 'Type'. I just need to aggregate the values based of ids. Please check my expected output.
– palpras
Jan 2 at 11:26
@palpras : Have you tried the query on your tables? What output did you get?
– Kaushik Nayak
Jan 2 at 12:03
@palpras : Have you tried the query on your tables? What output did you get?
– Kaushik Nayak
Jan 2 at 12:03
@ Kaushik Nayak . Thanks, I am able to get the required output :)
– palpras
Jan 3 at 12:11
@ Kaushik Nayak . Thanks, I am able to get the required output :)
– palpras
Jan 3 at 12:11
@ Kaushik Nayak : Is it feasible to get the same result without using the group by, just thinking from the perspective of making it more performance efficient
– palpras
Jan 4 at 9:19
@ Kaushik Nayak : Is it feasible to get the same result without using the group by, just thinking from the perspective of making it more performance efficient
– palpras
Jan 4 at 9:19
@palpras : There may be, but I don't think they would improve the performance any better and
group by is the most sensible thing to go for when you have multiple ids to aggregate. One option to speed up the query would be to add an index on id,type. You may ask a separate question if you face performance issues giving the details of the explain plan etc as performance purely depends on your data– Kaushik Nayak
Jan 4 at 11:02
@palpras : There may be, but I don't think they would improve the performance any better and
group by is the most sensible thing to go for when you have multiple ids to aggregate. One option to speed up the query would be to add an index on id,type. You may ask a separate question if you face performance issues giving the details of the explain plan etc as performance purely depends on your data– Kaushik Nayak
Jan 4 at 11:02
add a comment |
0
I would write this as:
select sum(case when num_As > 0 and num_Bs = 0 then 1 else 0 end) as num_A_only,
sum(case when num_As = 0 and num_Bs > 0 then 1 else 0 end) as num_B_only,
sum(case when num_As > 0 and num_Bs > 0 then 1 else 0 end) as num_A_and_B
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t;
If you want this in separate rows:
select (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end) as grp,
count(*)
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t
group by (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end);
answered Jan 2 at 12:01
Gordon LinoffGordon Linoff
790k35314418
Thanks, your answer also works :)
– palpras
Jan 3 at 12:12
add a comment |
0
I would write this as:
select sum(case when num_As > 0 and num_Bs = 0 then 1 else 0 end) as num_A_only,
sum(case when num_As = 0 and num_Bs > 0 then 1 else 0 end) as num_B_only,
sum(case when num_As > 0 and num_Bs > 0 then 1 else 0 end) as num_A_and_B
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t;
If you want this in separate rows:
select (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end) as grp,
count(*)
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t
group by (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end);
answered Jan 2 at 12:01
Gordon LinoffGordon Linoff
790k35314418
Thanks, your answer also works :)
– palpras
Jan 3 at 12:12
add a comment |
0
0
0
I would write this as:
select sum(case when num_As > 0 and num_Bs = 0 then 1 else 0 end) as num_A_only,
sum(case when num_As = 0 and num_Bs > 0 then 1 else 0 end) as num_B_only,
sum(case when num_As > 0 and num_Bs > 0 then 1 else 0 end) as num_A_and_B
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t;
If you want this in separate rows:
select (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end) as grp,
count(*)
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t
group by (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end);
answered Jan 2 at 12:01
Gordon LinoffGordon Linoff
790k35314418
I would write this as:
select sum(case when num_As > 0 and num_Bs = 0 then 1 else 0 end) as num_A_only,
sum(case when num_As = 0 and num_Bs > 0 then 1 else 0 end) as num_B_only,
sum(case when num_As > 0 and num_Bs > 0 then 1 else 0 end) as num_A_and_B
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t;
If you want this in separate rows:
select (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end) as grp,
count(*)
from (select id,
sum(case when type = 'A' then 1 else 0 end) as num_As,
sum(case when type = 'B' then 1 else 0 end) as num_Bs
from t
group by id
) t
group by (case when num_As > 0 and num_Bs = 0 then 'A_only'
when num_As = 0 and num_Bs > 0 then 'B_only'
when num_As > 0 and num_Bs > 0 then 'A_and_B'
end);
answered Jan 2 at 12:01
Gordon LinoffGordon Linoff
790k35314418
answered Jan 2 at 12:01
Gordon LinoffGordon Linoff
790k35314418
answered Jan 2 at 12:01
Gordon LinoffGordon Linoff
790k35314418
answered Jan 2 at 12:01
Gordon LinoffGordon Linoff
790k35314418
790k35314418
Thanks, your answer also works :)
– palpras
Jan 3 at 12:12
add a comment |
Thanks, your answer also works :)
– palpras
Jan 3 at 12:12
Thanks, your answer also works :)
– palpras
Jan 3 at 12:12
Thanks, your answer also works :)
– palpras
Jan 3 at 12:12
add a comment |
0
Based on your sample data the below will work as expected:
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
)
SELECT distinct
j1.type||j2.type as type,
COUNT( DISTINCT j1.ID ) OVER ( PARTITION BY j1.type||j2.type) AS Count
FROM jobs j1 inner join jobs j2 on j1.id = j2.id and j1.type <= j2.type
answered Jan 2 at 12:54
Ted at ORCL.ProTed at ORCL.Pro
1,35028
add a comment |
0
Based on your sample data the below will work as expected:
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
)
SELECT distinct
j1.type||j2.type as type,
COUNT( DISTINCT j1.ID ) OVER ( PARTITION BY j1.type||j2.type) AS Count
FROM jobs j1 inner join jobs j2 on j1.id = j2.id and j1.type <= j2.type
answered Jan 2 at 12:54
Ted at ORCL.ProTed at ORCL.Pro
1,35028
add a comment |
0
0
0
Based on your sample data the below will work as expected:
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
)
SELECT distinct
j1.type||j2.type as type,
COUNT( DISTINCT j1.ID ) OVER ( PARTITION BY j1.type||j2.type) AS Count
FROM jobs j1 inner join jobs j2 on j1.id = j2.id and j1.type <= j2.type
answered Jan 2 at 12:54
Ted at ORCL.ProTed at ORCL.Pro
1,35028
Based on your sample data the below will work as expected:
with jobs(id,type) AS
(
select 1, 'A' FROM DUAL UNION ALL
select 1, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'A' FROM DUAL UNION ALL
select 2, 'B' FROM DUAL UNION ALL
select 3, 'A' FROM DUAL UNION ALL
select 3, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 4, 'B' FROM DUAL UNION ALL
select 5, 'B' FROM DUAL
)
SELECT distinct
j1.type||j2.type as type,
COUNT( DISTINCT j1.ID ) OVER ( PARTITION BY j1.type||j2.type) AS Count
FROM jobs j1 inner join jobs j2 on j1.id = j2.id and j1.type <= j2.type
answered Jan 2 at 12:54
Ted at ORCL.ProTed at ORCL.Pro
1,35028
answered Jan 2 at 12:54
Ted at ORCL.ProTed at ORCL.Pro
1,35028
answered Jan 2 at 12:54
Ted at ORCL.ProTed at ORCL.Pro
1,35028
answered Jan 2 at 12:54
Ted at ORCL.ProTed at ORCL.Pro
1,35028
1,35028
add a comment |
add a comment |
0
Get 3 different counters for each case:
select
(select count(distinct id) from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
) countera,
(select count(distinct id) from jobs j
where ((select min(jobs.type) from jobs where jobs.id = j.id) = 'b')
) counterb,
(select count(distinct id) from jobs j
where (
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
)) counterab
from dual;
See the demo
To get 1 row for each counter:
select
'Count of IDs having just A as type :' descr,
count(distinct id) counter from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
union all
select
'Count of IDs having just B as type :' descr,
count(distinct id) from jobs j
where ((select min(jobs.type) counter from jobs where jobs.id = j.id) = 'b')
union all
select
'Count of IDs having A and B as type :' descr,
count(distinct id) counter from jobs j
where
(
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
);
See the demo
answered Jan 2 at 13:16
forpasforpas
17.8k3728
I am not getting the expected results from your query
– palpras
Jan 4 at 9:20
@palpras there are 2 demos where you see the expected results.
– forpas
Jan 4 at 9:34
add a comment |
0
Get 3 different counters for each case:
select
(select count(distinct id) from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
) countera,
(select count(distinct id) from jobs j
where ((select min(jobs.type) from jobs where jobs.id = j.id) = 'b')
) counterb,
(select count(distinct id) from jobs j
where (
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
)) counterab
from dual;
See the demo
To get 1 row for each counter:
select
'Count of IDs having just A as type :' descr,
count(distinct id) counter from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
union all
select
'Count of IDs having just B as type :' descr,
count(distinct id) from jobs j
where ((select min(jobs.type) counter from jobs where jobs.id = j.id) = 'b')
union all
select
'Count of IDs having A and B as type :' descr,
count(distinct id) counter from jobs j
where
(
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
);
See the demo
answered Jan 2 at 13:16
forpasforpas
17.8k3728
I am not getting the expected results from your query
– palpras
Jan 4 at 9:20
@palpras there are 2 demos where you see the expected results.
– forpas
Jan 4 at 9:34
add a comment |
0
0
0
Get 3 different counters for each case:
select
(select count(distinct id) from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
) countera,
(select count(distinct id) from jobs j
where ((select min(jobs.type) from jobs where jobs.id = j.id) = 'b')
) counterb,
(select count(distinct id) from jobs j
where (
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
)) counterab
from dual;
See the demo
To get 1 row for each counter:
select
'Count of IDs having just A as type :' descr,
count(distinct id) counter from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
union all
select
'Count of IDs having just B as type :' descr,
count(distinct id) from jobs j
where ((select min(jobs.type) counter from jobs where jobs.id = j.id) = 'b')
union all
select
'Count of IDs having A and B as type :' descr,
count(distinct id) counter from jobs j
where
(
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
);
See the demo
answered Jan 2 at 13:16
forpasforpas
17.8k3728
Get 3 different counters for each case:
select
(select count(distinct id) from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
) countera,
(select count(distinct id) from jobs j
where ((select min(jobs.type) from jobs where jobs.id = j.id) = 'b')
) counterb,
(select count(distinct id) from jobs j
where (
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
)) counterab
from dual;
See the demo
To get 1 row for each counter:
select
'Count of IDs having just A as type :' descr,
count(distinct id) counter from jobs j
where ((select max(jobs.type) from jobs where jobs.id = j.id) = 'a')
union all
select
'Count of IDs having just B as type :' descr,
count(distinct id) from jobs j
where ((select min(jobs.type) counter from jobs where jobs.id = j.id) = 'b')
union all
select
'Count of IDs having A and B as type :' descr,
count(distinct id) counter from jobs j
where
(
(select min(jobs.type) from jobs where jobs.id = j.id) = 'a'
and
(select max(jobs.type) from jobs where jobs.id = j.id) = 'b'
);
See the demo
answered Jan 2 at 13:16
forpasforpas
17.8k3728
edited Jan 2 at 14:22
answered Jan 2 at 13:16
forpasforpas
17.8k3728
answered Jan 2 at 13:16
forpasforpas
17.8k3728
answered Jan 2 at 13:16
forpasforpas
17.8k3728
17.8k3728
I am not getting the expected results from your query
– palpras
Jan 4 at 9:20
@palpras there are 2 demos where you see the expected results.
– forpas
Jan 4 at 9:34
add a comment |
I am not getting the expected results from your query
– palpras
Jan 4 at 9:20
@palpras there are 2 demos where you see the expected results.
– forpas
Jan 4 at 9:34
I am not getting the expected results from your query
– palpras
Jan 4 at 9:20
I am not getting the expected results from your query
– palpras
Jan 4 at 9:20
@palpras there are 2 demos where you see the expected results.
– forpas
Jan 4 at 9:34
@palpras there are 2 demos where you see the expected results.
– forpas
Jan 4 at 9:34
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%2f54004994%2fhow-to-group-the-rows-and-get-the-count-based-on-other-column-values-in-oracle%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
Your question is confusing. Please add expected output.
– Tim Biegeleisen
Jan 2 at 10:59
@TimBiegeleisen Output is simple as below: Count of Type A : 10 Count of Type B: 15 Count of type A and B : 5 Getting count of type 'A' and 'B' is simple enough, I am confused how to get the count of the ids in table having records for both type 'A' and type 'B' separately
– palpras
Jan 2 at 11:01
Your query is almost correct for the first 2 cases, I have to think about 3rd scenario A+B : SELECT distinct type, COUNT( DISTINCT ID ) OVER ( PARTITION BY type) AS Count FROM jobs
– Ted at ORCL.Pro
Jan 2 at 12:46