6 Replies Latest reply: Jan 9, 2013 6:23 AM by indra budiantho RSS

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

    983518
      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
          It is always best to post questions in the correct Forum. This is a common question in the 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
            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
              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 PL/SQL
              • 4. Re: Help on SQL query which split the string and count them by group by
                983518
                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
                  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
                    /* 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...