0

I have a table like the following:

id letter number
1       A      1
2       A      2
3       A      3
4       B      1
5       C      1
6       C      2

I need to count the number of id's where the letter A has a number 1 OR 2. That's the easy one, and I got it. But now I need to count id's where the letter A has a number 1 AND a number 2, etc. Then the same for each letter. So I would get:

letter count
A          3
C          2

I dont care about B since the count would be 1. Thanks.

Esoteric Screen Name
  • 6,082
  • 4
  • 29
  • 38
Nelson Canas
  • 1
  • Why is count of A = 3? Shouldn't it only count if 1 or 2 is present? – juergen d Aug 19 '12 at 15:58
  • What language and where's your code sample? – Chris Gessler Aug 19 '12 at 16:08
  • My fault. It should be counting the number of different values the letter has. Depending on what the user selects, this could be a combination of ORs and ANDs. So,it could be: – Nelson Canas Aug 20 '12 at 13:03
  • Ex 1: where A = 1 or A = 2 or A = 3 Ex 2: where A = 1 or A = 2 or B = 1 or C = 2 Ex 3: where A = 1 and A = 2 or A = 3 I am using Oracle 11g, with plain SQL I can do Ex 3 using Intersects, but that would get complicated and long, once more letters and values are selected. – Nelson Canas Aug 20 '12 at 13:26

1 Answers1

1

Use a combination of analytical functions (PARTITION BY) and GROUP BY:

with v_data as (
select 1 id, 'A' letter, 1 num from dual union all
select 2 id, 'A' letter, 2 num from dual union all
select 3 id, 'A' letter, 3 num from dual union all
select 4 id, 'B' letter, 1 num from dual union all
select 5 id, 'C' letter, 1 num from dual union all
select 6 id, 'C' letter, 2 num from dual
)
select letter, sum(cnt1) as cnt1, sum(cnt2) as cnt2, count(*) cnt_overall from (
select v1.*, 
  sum(case when num = 1 then 1 else 0 end) over (partition by letter) as cnt1, 
  sum(case when num = 2 then 1 else 0 end) over (partition by letter) as cnt2
 from v_data v1
) 
group by letter
having sum(cnt1) > 0 and sum(cnt2) > 0

Explanation:

  • the inner query returns alls rows and computes the number of occurrences of 1 / 2 for the row's id
  • the outer query computes the number of rows for a given id and get rids of the ids that don't have both values 1 and 2
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • This is correct, it should be marked as accepted. Looks like you might get unlucky with Nelson Canas being a new user and this is his first question and may never come back and accept it:( – Reimius Aug 23 '12 at 20:11