6 Replies Latest reply: Mar 5, 2013 7:44 PM by Frank Kulash RSS

    SQL Query with Distinct and Count is wrong.

    993527
      Hello,

      i have another problem with a query.
      Here the Data:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      CREATE      TABLE      TABLE_1
         
      (       "ORDER_NR"        VARCHAR2 (12)
      ,        "PRIORITY"        VARCHAR2 (2)
      ,        "WO_STATUS"        VARCHAR2 (1)
      ,        "STATUS_DATE"        DATE
      ,       "ART_NR"                      VARCHAR2 (9)
      ,       "DESCRIPTION"      VARCHAR2 (255)
      ,                 "PRICE"                     VARCHAR2 (10)
      );
      
      CREATE      TABLE      TABLE_2
      (     "ART_NR"            VARCHAR(9)
      ,     "MODELL"              VARCHAR2(10)
      ,     "MANUFACT"         VARCHAR2(20)
      );
      
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300612',     '12',     'U',        TO_DATE('05-FEB-13 10:22:39','DD-MON-RR HH24:MI:SS'),     '005231987',     '1ST ANNUAL SERVICE',   '5000.2546');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300638',     '05',     'U',        TO_DATE('05-FEB-13 11:38:39','DD-MON-RR HH24:MI:SS'),     '005667821',     '3RD ANNUAL SERVICE',   '5269.7856');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300638',     '12',     'U',        TO_DATE('06-FEB-13 12:38:39','DD-MON-RR HH24:MI:SS'),     '005667821',     '1ST BIENNIAL SERVICE', '1234.4468');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300638',     '12',     'U',        TO_DATE('07-FEB-13 13:38:39','DD-MON-RR HH24:MI:SS'),     '005667821',     '3RD ANNUAL SERVICE',   '4366.7856');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300762',     '12',     'U',        TO_DATE('22-FEB-13 14:55:48','DD-MON-RR HH24:MI:SS'),     '018743356',     '3RD ANNUAL SERVICE',   '4462.8632');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300766',     '12',     'U',        TO_DATE('22-FEB-13 08:32:13','DD-MON-RR HH24:MI:SS'),     '018743356',     '2ND ANNUAL SERVICE',   '8762.6643');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300766',     '05',     'U',        TO_DATE('23-FEB-13 12:32:13','DD-MON-RR HH24:MI:SS'),     '018743356',     '1ST BIENNIAL SERVICE', '3425.6643');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300766',     '12',     'U',        TO_DATE('24-FEB-13 14:32:13','DD-MON-RR HH24:MI:SS'),     '018743356',     '2ND BIENNIAL SERVICE', '6678.6643');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300612',     '12',     'U',        TO_DATE('06-FEB-13 10:22:39','DD-MON-RR HH24:MI:SS'),     '005231987',     '1ST ANNUAL SERVICE',   '5000.2546');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300638',     '05',     'U',        TO_DATE('05-FEB-13 11:38:39','DD-MON-RR HH24:MI:SS'),     '005667821',     '3RD ANNUAL SERVICE',   '5269.7856');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300638',     '12',     'U',        TO_DATE('06-FEB-13 12:38:39','DD-MON-RR HH24:MI:SS'),     '005667821',     '1ST BIENNIAL SERVICE', '1234.4468');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300638',     '12',     'U',        TO_DATE('07-FEB-13 13:38:39','DD-MON-RR HH24:MI:SS'),     '005667821',     '3RD ANNUAL SERVICE',   '4366.7856');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300762',     '12',     'U',        TO_DATE('22-FEB-13 14:55:48','DD-MON-RR HH24:MI:SS'),     '018743356',     '3RD ANNUAL SERVICE',   '4462.8632');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300766',     '12',     'U',        TO_DATE('22-FEB-13 08:32:13','DD-MON-RR HH24:MI:SS'),     '018743356',     '2ND ANNUAL SERVICE',   '8762.6643');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300766',     '05',     'U',        TO_DATE('23-FEB-13 12:32:13','DD-MON-RR HH24:MI:SS'),     '018743356',     '1ST BIENNIAL SERVICE', '3425.6643');
      INSERT      INTO      TABLE_1      (ORDER_NR,              PRIORITY, WO_STATUS,  STATUS_DATE,                                             ART_NR,           DESCRIPTION,            PRICE) 
                    VALUES           ('1KKA1Z300766',     '12',     'U',        TO_DATE('24-FEB-13 14:32:13','DD-MON-RR HH24:MI:SS'),     '018743356',     '2ND BIENNIAL SERVICE', '6678.6643');
      
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT1');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT2');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT3');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT4');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT5');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT6');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005667821',     'LASER',          'MANUFACT1');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005667821',     'LASER',          'MANUFACT2');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005667821',     'LASER',          'MANUFACT3');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005667821',     'LASER',          'MANUFACT4');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT1');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT2');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT3');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT4');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT5');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT6');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT1');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT2');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT3');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT4');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT5');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005231987',     'X-RAY1',          'MANUFACT6');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005667821',     'LASER',          'MANUFACT1');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005667821',     'LASER',          'MANUFACT2');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005667821',     'LASER',          'MANUFACT3');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('005667821',     'LASER',          'MANUFACT4');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT1');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT2');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT3');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT4');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT5');
      INSERT     INTO      TABLE_2      (ART_NR,            MODELL,         MANUFACT)
                    VALUES           ('018743356',     'VACCUM',          'MANUFACT6');
      COMMIT;
      And my query:
      SELECT T1.ART_NR
      , T2.MODELL
      , SUM(ROUND(T1.PRICE, 2)) AS TOTAL_PRICE
      , COUNT(*) AS QTY
      , TO_CHAR(T1.STATUS_DATE, 'MON-RR') AS MONTH
      FROM TABLE_1 T1, TABLE_2 T2
      WHERE T1.WO_STATUS = 'U'
      AND T1.ART_NR = T2.ART_NR
      AND TO_CHAR(T1.STATUS_DATE, 'MON-RR') = 'FEB-13'
      GROUP BY T2.MODELL
      , T1.ART_NR
      , TO_CHAR(T1.STATUS_DATE, 'MON-RR')
      And the result:
      ART_NR      MODELL     TOTAL_PRICE        QTY     MONTH
      ---------        ----------       -----------                ---------- ------
      018743356 VACCUM     559916.16            96        FEB-13 
      005667821 LASER        173936.48            48        FEB-13 
      005231987 X-RAY1          120006             24        FEB-13
      My problem now is, the OTY field ist wrong it should count how often the equipment was in service in FEB-13 and group it by "MODELL" the MANUFACT field is not interesting for me, but this ist my problem, one Modell can have multible Manufacter and so i got a wrong count for my QTY.

      The next step i need is to group the result also by Service type (annual or biennial), like this:
      ART_NR      MODELL     TOTAL_PRICE        QTY     MONTH   SERVICE_TYPE
      ---------        ----------       -----------                ---------- ------        ---------------------
      018743356 VACCUM      1234.56               4         FEB-13     ANNUAL
      018743356 VACCUM      4423.48               10       FEB-13     BIENNIAL
      005667821 LASER         4783.11               2         FEB-13     ANNUAL
      005667821 LASER         1123.77               22       FEB-13      BIENNIAL
      005231987 X-RAY1        8966.12               6        FEB-13      ANNUAL
      005231987 X-RAY1        7826.44              12        FEB-13      BIENNIAL
      This values are only out of my head, not the table, only to show what i need.

      Thanks for your help.

      Greets Reinhard
        • 1. Re: SQL Query with Distinct and Count is wrong.
          Frank Kulash
          Hi, Reinhard,
          990524 wrote:
          Hello,

          i have another problem with a query.
          Here the Data:
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
          CREATE      TABLE      TABLE_1
          ...
          Thanks for posting the version, and the CREATE TABLE and INSERT statements; that's very helpful.
          Always post the exact results you want from the sameple data.
          ... This values are only out of my head, not the table, only to show what i need.
          If the values are not the actual results you want from the given sample data, then they do not show what you need. Spend 2 minutes (if it comes to that much) figuring out what the correct results are, and post them. Post less sample data, if that helps. You could probably give a good picture of this problem with only 10 or 15 rows in the tables.
          • 2. Re: SQL Query with Distinct and Count is wrong.
            993527
            Hi Frank,

            you always here and fast.
            This is the result that i expect:
            ART_NR       MODELL  TOTAL_COST   QTY  MONTH  SERVICE_TYPE
            005231987   X-RAY1   10000.51         2      FEB-13   ANNUAL
            005667821   LASER     19273.14        4       FEB-13   ANNUAL
            005667821   LASER     2468.89          2       FEB-13   BIENNIAL
            018743356   VACCUM   26451.06       4       FEB-13   ANNUAL
            018743356   VACCUM   20208.66       4       FEB-13   BIENNIAL
            The content of Table_1 should be grouped by ART_NR and SERVICE_TYPE and the should calculated the TOTAL_COST an the QTY should be counted.
            The service type need to be extracted from the Description field in Table_1, this look complex...

            Thanks for you help.

            Reinhard.
            • 3. Re: SQL Query with Distinct and Count is wrong.
              AlbertoFaenza
              Hi Reinhard,

              is this what you want?
              WITH got_table_2 AS
              (
                 SELECT DISTINCT art_nr, modell
                   FROM table_2
              )
              SELECT t1.art_nr
                   , t2.modell
                   , SUM(ROUND(t1.price, 2)) AS total_price
                   , TO_CHAR(t1.status_date, 'MON-RR') AS mnth
                   , count(*) qty
                   , REGEXP_SUBSTR(t1.description,'(ANNUAL|BIENNIAL)') service_type
                FROM table_1 t1, got_table_2 t2
               WHERE t1.wo_status = 'U'
                 AND TO_CHAR(t1.status_date, 'MON-RR') = 'FEB-13'
                 AND t1.art_nr = t2.art_nr
               GROUP BY t1.art_nr
                      , t2.modell
                      , REGEXP_SUBSTR(t1.description,'(ANNUAL|BIENNIAL)')
                      , TO_CHAR(t1.status_date, 'MON-RR')
               ORDER BY t1.art_nr
                      , t2.modell
                      , REGEXP_SUBSTR(t1.description,'(ANNUAL|BIENNIAL)')
                      , TO_CHAR(t1.status_date, 'MON-RR');
              
              ART_NR    MODELL     TOTAL_PRICE MNTH          QTY SERVICE_TYPE        
              --------- ---------- ----------- ------ ---------- --------------------
              005231987 X-RAY1         10000.5 FEB-13          2 ANNUAL              
              005667821 LASER         19273.16 FEB-13          4 ANNUAL              
              005667821 LASER           2468.9 FEB-13          2 BIENNIAL            
              018743356 VACCUM        26451.04 FEB-13          4 ANNUAL              
              018743356 VACCUM        20208.64 FEB-13          4 BIENNIAL    
              Regards.
              Al
              • 4. Fan Trap
                Frank Kulash
                Hi,

                Here's one way:
                WITH    got_groups  AS
                (
                     SELECT  art_nr
                     ,     TRUNC (status_date, 'MONTH')     AS month
                     ,     CASE
                              WHEN  UPPER (description) LIKE '%ANNUAL%'
                                                     THEN  'ANNUAL'
                              WHEN  UPPER (description) LIKE '%BIENNIAL%'
                                                     THEN  'BIENNIAL'
                          END                    AS service_type
                     ,     TO_NUMBER (price)          AS price
                     FROM     table_1
                     WHERE     status_date     >= DATE '2013-02-01'
                     AND     status_date     <  DATE '2013-03-01'
                )
                ,       table_2_summary  AS
                (
                     SELECT DISTINCT       art_nr, modell
                     FROM               table_2
                )
                SELECT       g.art_nr
                ,       s.modell
                ,       ROUND ( SUM (g.price)
                          , 2
                          )          AS total_price
                ,       COUNT (*)          AS qty
                ,       g.month
                ,       service_type
                FROM       got_groups       g
                JOIN       table_2_summary  s     ON  s.art_nr  = g.art_nr  
                GROUP BY  g.art_nr
                ,            s.modell
                ,            g.month
                ,       g.service_type
                ;
                The reason why your aggregates were originally too high is that you have a many-to-many relationship between the tables. The tables are related only by art_nr, but art_nr is not unique in either table. Look at art_nr '005231987', example. There ate 2 rows in table_1 with that art_nr, and 6 rows in table_2 with the same art_nr. If we join on art_nr, then both of the rows in table_1 will match each of the 6 rows in table_2, so the COUNT will be 2 * 6 = 12, and in the SUM, each of the numbers from table_1 will get added 6 times.

                Why is table_2 designed the way it is? Cn there be multiple modells for the same art_nr? If so, what would you want for output? If there can only be 1 modell for each art_nr, then a better design would be to have a table that just had one row per art_nr, and included the modell column, and another table to show which manufacturers produce each art_nr. In this problem, you wouldn't need the manufacturers table, and the other table already has unique art_nrs, so you wouldn't need anything like the sub-query table_2_summary.

                Don't store price in a VARCHAR2 column. Storing NUMBERs in a VARCHAR2 column is just asking for problems. Why not use a NUMBER column instead.

                You'll notice that I used ROUND (SUM ... where you use SUM ( ROUND. The results might be a little different because of rounding errors. ROUND ( SUM only has to call ROUND once per group (5 times in this example) instead of once per row (16 times in this example). The less rounding you do, the less rounding error creeps in. Also, since there are fewer function calls, it's more convenient. (Of course, you'll never notice the difference between calling ROUND 5 times or 16 times, but in a real-life exampe, the difference could be between calling it 50 times or calling it 16000 times.) If you really need to use SUM (ROUND, you can.
                • 5. Re: Fan Trap
                  993527
                  Hi,

                  wow thats great.
                  Thank You Frank.

                  In my Database this query works like a charm, but i have an issue, sometimes the round command dont work then i get a total price of 1231.0000000000001, i have
                  tried to TRUNC the value but it is still the same value.
                  And i put a another row in the query that calculates the average price per model:
                  , ROUND ( SUM (g.price) / COUNT(*) ,2 ) AS average
                  and at this row,i have the same issue with wrong ROUND.

                  Do you have an idea in this case?

                  Thanks
                  • 6. Re: Fan Trap
                    Frank Kulash
                    Hi,
                    990524 wrote:
                    Hi,

                    wow thats great.
                    Thank You Frank.

                    In my Database this query works like a charm, but i have an issue, sometimes the round command dont work then i get a total price of 1231.0000000000001, i have
                    tried to TRUNC the value but it is still the same value.
                    Sorry, I can't re-create the problem.
                    And i put a another row in the query that calculates the average price per model:
                    , ROUND ( SUM (g.price) / COUNT(*) ,2 ) AS average
                    Why not simply
                    , ROUND (AVG (g.price), 2)   AS average
                    ?
                    and at this row,i have the same issue with wrong ROUND.

                    Do you have an idea in this case?
                    No, sorry. If I can't get the same behavior myself, there's not much I can do.

                    Instead of ROUND, you can use TO_CHAR to display (e.g.) 2 digits after the decimal point. TO_CHAR will automatically round the result. Your front end (e.g., the SQL*Plus COLUMN ... FORMAT command) can probably do the same thing.