This discussion is archived
6 Replies Latest reply: Jan 9, 2013 4:23 AM by indra budiantho RSS

Help on SQL query which split the string and count them by group by

983518 Newbie
Currently Being Moderated
Hi,

I need a help on build a query for below table.

Table: Contains two columns, 1. Department, 2. Product.

Dept | Product
---------------------
Abc | p3
Abc | p1
Abc | p3:p1
Abc | p2:p4
Abc | p5
Abc | p1

I want final output count of product and which is group by. (like below)

Product | Count
----------------------
p1 | 3
p2 | 2
p3 | 1
p4 | 1
p5 | 1

Any one master in this kind of queries please?
  • 1. Re: Help on SQL query which split the string and count them by group by
    CraigB Guru
    Currently Being Moderated
    It is always best to post questions in the correct Forum. This is a common question in the SQL and PL/SQL Forum. A quick search here would have given you your answer. Your query will look something like:
    SELECT product, count(PRODUCT) as COUNT
    from <YOUR_TABLE>
    GROUP BY product;
    Hope this helps.
    Craig...
  • 2. Re: Help on SQL query which split the string and count them by group by
    983518 Newbie
    Currently Being Moderated
    Hi Craig,

    Thanks for your reply on this. And as you said that, this is not normal query from SQL/PL SQL. Please understand my query one more time. Your SQL query give result just like normal group by result like below.

    SELECT product, count(PRODUCT) as COUNT
    from <YOUR_TABLE>
    GROUP BY product;

    Product | Count
    -----------------------
    p1 | 2
    p2:p4 | 1
    p3 | 1
    p3:p1 | 1
    p5 | 1
    This is not what is expected.

    But, below is the result which I am expecting for. Here I am expecting first split the products with ':' delimiter and then count them individually.

    Product | Count
    -----------------------
    p1 | 3
    p2 | 2
    p3 | 1
    p4 | 1
    p5 | 1


    Please let me know if you have any question regarding this.
  • 3. Re: Help on SQL query which split the string and count them by group by
    user346369 Expert
    Currently Being Moderated
    What does p2:p4 mean? That it should be counted both as p2 and p4?

    How do you get a count of 2 for p2?

    What does p3:p1 mean?


    You really should post your question SQL and PL/SQL
  • 4. Re: Help on SQL query which split the string and count them by group by
    983518 Newbie
    Currently Being Moderated
    p1 to p5 are product list and those are string values for this column. Unfortunately some records are having more than 1 product lists like (p1:p5, p2:p3:p5) with ':' delimiter (you can assume here ',' or other delimiters).

    So here I want to get count by all listed products.

    I hope you understood this problem.
  • 5. Re: Help on SQL query which split the string and count them by group by
    983518 Newbie
    Currently Being Moderated
    And sorry... my result should be like below. what i given earlier was wrong. once again sorry fro that


    Product | Count
    p1 | 3
    p2 | 1
    p3 | 2
    p4 | 1
    p5 | 1
  • 6. Re: Help on SQL query which split the string and count them by group by
    indra budiantho Expert
    Currently Being Moderated
    /* Formatted on 1/9/2013 7:22:12 PM (QP5 v5.139.911.3011) */
    WITH T AS (SELECT 'Abc' AS Dept, 'p3' AS Product FROM DUAL
               UNION ALL
               SELECT 'Abc' AS Dept, 'p1' AS Product FROM DUAL
               UNION ALL
               SELECT 'Abc' AS Dept, 'p3:p1' AS Product FROM DUAL
               UNION ALL
               SELECT 'Abc' AS Dept, 'p2:p4' AS Product FROM DUAL
               UNION ALL
               SELECT 'Abc' AS Dept, 'p5' AS Product FROM DUAL
               UNION ALL
               SELECT 'Abc' AS Dept, 'p1' AS Product FROM DUAL)
      SELECT Q.*, (SELECT COUNT (*)
                     FROM T
                    WHERE INSTR (T.Product, Q.Product) > 0)
                     TOT
        FROM (    SELECT Dept, REGEXP_SUBSTR (
                                              Product,
                                              '[^:]+',
                                              1,
                                              LEVEL
                                             )
                                  AS PRODUCT
                    FROM T
              CONNECT BY LEVEL <=
                            NVL (
                                 LENGTH (REGEXP_REPLACE (Product, '[^:]', NULL)),
                                 0
                                )
                            + 1
                GROUP BY Dept, REGEXP_SUBSTR (
                                              Product,
                                              '[^:]+',
                                              1,
                                              LEVEL
                                             )) q
    ORDER BY Q.Product
    output:
    Abc     p1     3
    Abc     p2     1
    Abc     p3     2
    Abc     p4     1
    Abc     p5     1

    u'd put the question in the PL/SQL FORUM...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points