I need the count of values

Question

I have data in my table in oracle like below

A_CODE    B_M   P_id
------    ----  ------
123        A     1
123        A     2
123        B     5
678        B     3
678        C     3
678        B     4
123        BC    2

The value "BC" is B and C. The data is not normalized so we need to count it as B and C. I need the counts to be displayed as below per A_CODE

A_CODE   B_M   COUNT
-------  ---- -------
123        A    2
123        B    2
123        C    1
678        B    2
678        C    1

How can i do this in Oracle?

You should consider normalize your table. And dodnt even have a `,` to use as separator :(. http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle — Juan Carlos Oropeza, Nov 17 '16 at 03:40
The actual data in B_M column is supposed to be a single character (even though it's clubbed in your table), is that right? — Gurwinder Singh, Nov 17 '16 at 03:45
@Juan Carlos Oropeza, tell me how to do by ignoring the BC value in table — Chris, Nov 17 '16 at 03:49

score 3 · Answer 1 · edited Nov 17 '16 at 09:59

3

You should use CONNECT BY and CONNECT_BY_ROOT. I hope this helps:

SELECT A_CODE, B_M, COUNT (*)
    FROM (    SELECT A_CODE, SUBSTR (CONNECT_BY_ROOT (B_M), LEVEL, 1) B_M
                FROM your_table
          CONNECT BY LEVEL <= LENGTH (B_M))
          WHERE B_M IS NOT NULL
GROUP BY A_CODE, B_M
ORDER BY A_CODE;

edited Nov 17 '16 at 09:59

René Hoffmann

2,766
2
20
43

answered Nov 17 '16 at 04:14

T.Q.H

89
8

Gurwinder Singh · Accepted Answer · 2016-11-17T09:19:17.210

0

Please try below:

SELECT A_CODE, B_M, COUNT(*) "COUNT" FROM
(SELECT A_CODE, B_M
FROM
  (SELECT A_CODE,
    SUBSTR(B_M,x.LVL,1) B_M
  FROM my_table t,
    (SELECT LEVEL LVL FROM dual
    CONNECT BY LEVEL <=
        (SELECT MAX(LENGTH(B_M)) FROM my_table)
    ) x
  WHERE t.B_M is not null
  )
WHERE B_M IS NOT NULL
UNION ALL
SELECT A_CODE, B_M FROM my_table WHERE B_M IS NULL
)
GROUP BY A_CODE,
  B_M
ORDER BY A_CODE, B_M;

edited Nov 17 '16 at 09:19

answered Nov 17 '16 at 03:59

Gurwinder Singh

38,557
6
51
76

When i did this i still get the result as 123 A 1 , 123 A 1, 123 B 1, 123 B 1 instead of 123 A 2, 123 B 2 , ... – Chris Nov 17 '16 at 04:48
The B_M values should not be repeated twice like (123 A 1, 123 A 1) instead the count should be displayed as 123 A 2 (as A is repeated twice for 123) – Chris Nov 17 '16 at 04:56

score 0 · Answer 3 · edited Nov 17 '16 at 09:57

Besides having my_table, you create a table with all possible values. That is:

P_VAL
-----
  A
  B
  C

Then, you should be able to obtain the count of occurrences with a join. Something like:

with my_table
  as (      select '123' a_code, 'A' b_m, '1' p_id from dual
      union select '123' a_code, 'A' b_m, '2' p_id from dual
      union select '123' a_code, 'B' b_m, '5' p_id from dual
      union select '678' a_code, 'B' b_m, '3' p_id from dual
      union select '678' a_code, 'C' b_m, '3' p_id from dual
      union select '678' a_code, 'B' b_m, '4' p_id from dual
      union select '123' a_code, 'BC' b_m, '2' p_id from dual)
    ,possible_values
  as (      select 'A' p_val from dual
      union select 'B' p_val from dual
      union select 'C' p_val from dual)
select a_code
      ,p_val b_m
      ,count('X') count
  from my_table
  join possible_values
    on instr(b_m,p_val) > 0
 group by a_code,p_val
 order by a_code,p_val;

What if we don't know the possible values in advance? – Gurwinder Singh Nov 17 '16 at 17:06 — Gurwinder Singh, Nov 17 '16 at 17:06

score 0 · Answer 4 · answered Nov 17 '16 at 08:57

One option is to join this table to a data set that provides you with the normalised structure you need.

with cte_normaliser as (
  select 'A'  B_M, 'A' 'B_M_norm from dual union all
  select 'B'  B_M, 'B' 'B_M_norm from dual union all
  select 'C'  B_M, 'C' 'B_M_norm from dual union all
  select 'BC' B_M, 'B' 'B_M_norm from dual union all
  select 'BC' B_M, 'C' 'B_M_norm from dual)
select my_table.A_CODE,
       n.B_M_norm,
       count(*)
from   my_table join
       cte_normaliser n on n.B_M = my_table.B_M
group by my_table.A_CODE,
       n.B_M_norm;

Using a fixed data set like that might not be feasible if you have a large number of variable code combination, though, and that data set might need to be built dynamically.

@Gurwinder as I said, the normalising data set might need to be built dynamically. — David Aldridge, Nov 17 '16 at 20:12

I need the count of values

4 Answers4