Results 1 to 8 of 8

Thread: SQL help

  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: SQL help

    I am having trouble writing some SQL and I was wondering if I could get some help.

    I have a db with a column of 10 character strings, i.e. 'WANMERTYPO', 'POWANMTYER', etc. each one of these strings represent 5, two character pairs. (i.e. 'WA' 'NM' 'ER' 'TY' 'PO')

    What I'd like to do is do a count of each 2 character pairs, i.e.:

    pair1
    count string
    22 WA
    7 PO

    pair2
    count string
    1 NM
    5 WA

    pair3
    count string
    4 ER
    14 NM


    And so on to pair5

    I can use a substr to grab each pair, but I can't seem to get a count of them. Any ideas??

    Thank you!
    Last edited by jmikula019; 03-02-12 at 00:47.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any ideas??
    I am not clear on what you have or what you desire/expect.
    post CREATE TABLE & INSERT statements so we have a test case to code against.
    Based upon test data from posted INSERT statements, post expected/desired result & explain why.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2012
    Posts
    4

    More Information

    UPDATE: I have figured out how to get a count each pair...

    I can write 5 individual statements:

    select count(*), pair1 from (select SUBSTR ( content10,1, 2 ) as pair1 from $A$) group by pair1;

    select count(*), pair2 from (select SUBSTR ( content10,3, 2 ) as pair2 from $A$) group by pair2;
    .
    .
    .


    But I'd like a way to see all results from 1 SQL statement...
    Last edited by jmikula019; 03-02-12 at 00:49.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I can write 5 individual statements:
    I would like to see that including actual results via COPY & PASTE.

    >select count(*), pair1 from (select SUBSTR ( content10,0, 2 ) as pair1 from $A$) group by pair1;
    What is "$A$" from line above?
    how many rows get returned from SQL above? Please post proof.

    >select count(*), pair2 from (select SUBSTR ( content10,0, 2 ) as pair2 from $A$) group by pair2;
    Why does SQL above have exact same value in SUBSTR() function as previous SQL?

    how many total rows in base table?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2012
    Posts
    4
    COUNT(*) PAIR1
    511016 AU
    381497 CE
    610643 FW
    2493108 HA
    400305 LG
    346164 OD
    44174 SP
    1189440 TL


    I fixed the syntax in the previous sql... Above is the output of a single query... Sorry.. the $A$ is a aliasing for the database name. Also, content10 is the column name.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about something like...

    Not tested.
    Code:
    SELECT pair_no
         , SUBSTR(content10 , pair_no * 2 - 1 , 2) AS pair
         , COUNT(*)
     FROM  $A$
     CROSS JOIN
          (SELECT 1 AS pair_no FROM dual UNION ALL
           SELECT 2 FROM dual UNION ALL
           SELECT 3 FROM dual UNION ALL
           SELECT 4 FROM dual UNION ALL
           SELECT 5 FROM dual
          )
     GROUP BY
           pair_no
         , SUBSTR(content10 , pair_no * 2 - 1 , 2)
    ;

  7. #7
    Join Date
    Mar 2012
    Posts
    4
    looks like it would work, but I can't use a Cartesian join... UI won't allow it..


    Thank you....

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... UI won't allow it..
    What was the reason of not allowing?
    What error message(s) did you got?
    How about replacing "CROSS JOIN" with a comma","?
    Last edited by tonkuma; 03-02-12 at 03:25.

Posting Permissions

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