Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    34

    Unanswered: Combine Columns and get the count

    Below is how my input data looks like in the table. I need to get the ouput as i mentioned below using an sql

    Col1 CODE1 CODE2 CODE3 CODE4 CODE5 CODE6 CODE7
    --------- ----- ----- ----- ----- ----- ----- -----
    708523301 A6NPT A8PP1 D8ORT
    708523302 A6NPT A6NPC P8002 A9100 D9BLK
    708523303 C4039 A6NPT D4007
    708523303 D0E43 A6NPT A6NPC P8002 D9BLK
    708523307 A6NPT P8002 D9BLK D4007
    708523307 A6NPT P8002 D9BLK D4007 D4006


    I want my output to be like the below. I need to count the codes that start with 'D' or 'E' or 'A' for each distinct record in col1.
    How can we do this using SQL query

    Col1 CODE COUNT
    708523301 D8ORT 1
    708523302 D9BLK 1
    708523303 D4007 1
    708523303 D0E43 1
    708523303 D9BLK 1
    708523307 D9BLK 2
    708523307 D4007 2
    708523307 D4006 1

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Code:
    with t (col1, code1, code2, code3, code4, code5) as (values
      (708523301, 'A6NPT', 'A8PP1', 'D8ORT', null, null)
    , (708523302, 'A6NPT', 'A6NPC', 'P8002', 'A9100', 'D9BLK')
    , (708523303, 'C4039', 'A6NPT', 'D4007', null, null)
    , (708523303, 'D0E43', 'A6NPT', 'A6NPC', 'P8002', 'D9BLK')
    , (708523307, 'A6NPT', 'P8002', 'D9BLK', 'D4007', null)
    , (708523307, 'A6NPT', 'P8002', 'D9BLK', 'D4007', 'D4006')
    )
    , c(i) as (values 1, 2, 3, 4, 5)
    select col1, code, count(1) count
    from (
    select 
      t.col1
    , decode(c.i, 1, t.code1, 2, t.code2, 3, t.code3, 4, t.code4, 5, t.code5) code
    from t, c
    )
    where substr(code, 1, 1) in ('D', 'E', 'A')
    group by col1, code
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •