12 Replies Latest reply: May 16, 2012 5:24 AM by Igor.M RSS

    difference between two columns on a different table

    927867
      Hi guys,

      I need help on this issue. I tried the MINUS operator but it wont give me the difference.

      Here's my query

      SELECT n_grantedaid
      FROM marketingaidgrant
      WHERE nfp_periodid = 169
      AND nfp_unitid = u.ip_unitid
      MINUS
      SELECT SUM(n_aid)
      FROM marketingaidapplication
      WHERE nf_periodid = 169
      AND nf_unitid = u.ip_unitid
      AND to_char(D_DATECREATED, 'yyyymm') in ('201202')

      For example:
      n_grantedaid is 7000
      n_aid is 3975
      Difference would be 3025


      Thanks for helping..


      Ed
        • 1. Re: difference between two columns on a different table
          €$ħ₪
          SELECT n_grantedaid
          FROM marketingaidgrant
          WHERE nfp_periodid = 169
          AND nfp_unitid = u.ip_unitid
          MINUS
          SELECT SUM(n_aid)
          FROM marketingaidapplication
          WHERE nf_periodid = 169
          AND nf_unitid = u.ip_unitid
          AND to_char(D_DATECREATED, 'yyyymm') in ('201202')

          these two are giving different rows , you cannot subtract like this

          try this

          SELECT sum(n_grantedaid) - SUM(n_aid)
          FROM marketingaidgrant A, marketingaidapplication B
          WHERE a.nfp_periodid = 169
          AND a.nfp_unitid = u.ip_unitid
          and b.nf_periodid = 169
          and b.nf_unitid = u.ip_unitid
          and to_char(b.D_DATECREATED, 'yyyymm') in ('201202')
          and a.nfp_unitid =b.nf_unitid 
          and a.nfp_periodid=b.nf_periodid
          Edited by: Ekh on May 16, 2012 3:17 PM
          • 2. Re: difference between two columns on a different table
            Igor.M
            SELECT (SELECT n_grantedaid
                      FROM marketingaidgrant
                     WHERE nfp_periodid = 169 AND nfp_unitid = u.ip_unitid)
                   - (SELECT SUM (n_aid)
                        FROM marketingaidapplication
                       WHERE     nf_periodid = 169
                             AND nf_unitid = u.ip_unitid
                             AND TO_CHAR (D_DATECREATED, 'yyyymm') IN ('201202'))
                      diff
            FROM DUAL;
            or
            SELECT n_grantedaid - sum_ diff
            from
                (
                    SELECT n_grantedaid
                    FROM marketingaidgrant
                    WHERE nfp_periodid = 169 AND nfp_unitid = u.ip_unitid
                  ) a,
                  (
                  SELECT SUM (n_aid) sum_
                  FROM marketingaidapplication
                  WHERE     nf_periodid = 169
                             AND nf_unitid = u.ip_unitid
                             AND TO_CHAR (D_DATECREATED, 'yyyymm') IN ('201202')
                  ) b;
            • 3. Re: difference between two columns on a different table
              927867
              so how can i subtract it? any help is appreciated..

              thanks..
              • 4. Re: difference between two columns on a different table
                Hoek
                Welcome to the forum.

                Why are you using MINUS here?
                You could just join both tables and subtract both columns.
                SELECT a.n_grantedaid-SUM(b.n_aid)
                ...
                FROM   marketingaidgrant a
                ,      marketingaidapplication b
                WHERE ...
                Also, where does this column come from: u.ip_unitid?
                • 5. Re: difference between two columns on a different table
                  927867
                  Hi igor,

                  thanks for the thought..

                  this is my whole query.. and im not sure where to put dual.. :(


                  SELECT DISTINCT u.c_name "Forhandler"
                  , (SELECT n_grantedaid
                  FROM marketingaidgrant
                  WHERE nfp_periodid = 169
                  AND nfp_unitid = u.ip_unitid) "Tildelt"
                  , (SELECT SUM(n_aid)
                  FROM marketingaidapplication
                  WHERE nf_periodid = 169
                  AND nf_unitid = u.ip_unitid
                  AND to_char(D_DATECREATED, 'yyyymm') in ('201202') ) "Benyttet"
                  --, (SELECT round(SUM(n_aid) / SUM(n_aid) over () * 100,2)
                  -- FROM marketingaidapplication
                  -- WHERE nf_periodid = 169
                  -- AND nf_unitid = u.ip_unitid) "% Benyttet"
                  , (SELECT n_grantedaid
                  FROM marketingaidgrant
                  WHERE nfp_periodid = 169
                  AND nfp_unitid = u.ip_unitid)
                  -
                  (SELECT SUM(n_aid)
                  FROM marketingaidapplication
                  WHERE nf_periodid = 169
                  AND nf_unitid = u.ip_unitid
                  AND to_char(D_DATECREATED, 'yyyymm') in ('201202')) "Tilgjengelig"
                  FROM roleuser ru, unit u, role r, userinfo ui, unit_groups ug, groups g
                  WHERE ru.nf_unitid = u.ip_unitid
                  AND ru.nf_roleid = r.ip_roleid
                  AND ru.nf_userid = ui.ip_userid
                  AND ru.nf_unitid = ug.nf_unitid
                  AND ug.nf_groupid = g.ip_groupid
                  AND ui.n_internal = 0
                  AND ui.nf_statusid = 1
                  AND u.nf_statusid = 1
                  AND ug.nf_groupid = 10
                  AND r.ip_roleid = 399
                  ORDER BY u.c_name;
                  • 6. Re: difference between two columns on a different table
                    Igor.M
                    DUAL is not need for this. But you need check for return -> must only one row.
                      SELECT DISTINCT
                             u.c_name "Forhandler",
                             (SELECT n_grantedaid
                                FROM marketingaidgrant
                               WHERE nfp_periodid = 169 AND nfp_unitid = u.ip_unitid)
                                "Tildelt",
                             (SELECT SUM (n_aid)
                                FROM marketingaidapplication
                               WHERE     nf_periodid = 169
                                     AND nf_unitid = u.ip_unitid
                                     AND TO_CHAR (D_DATECREATED, 'yyyymm') IN ('201202'))
                                "Benyttet"--, (SELECT round(SUM(n_aid) / SUM(n_aid) over () * 100,2)
                                          -- FROM marketingaidapplication
                                          -- WHERE nf_periodid = 169
                                          -- AND nf_unitid = u.ip_unitid) "% Benyttet"
                             ,
                             (SELECT n_grantedaid
                                FROM marketingaidgrant
                               WHERE nfp_periodid = 169 AND nfp_unitid = u.ip_unitid)
                             - (SELECT SUM (n_aid)
                                  FROM marketingaidapplication
                                 WHERE     nf_periodid = 169
                                       AND nf_unitid = u.ip_unitid
                                       AND TO_CHAR (D_DATECREATED, 'yyyymm') IN ('201202'))
                                "Tilgjengelig"
                        FROM roleuser ru,
                             unit u,
                             role r,
                             userinfo ui,
                             unit_groups ug,
                             groups g
                       WHERE     ru.nf_unitid = u.ip_unitid
                             AND ru.nf_roleid = r.ip_roleid
                             AND ru.nf_userid = ui.ip_userid
                             AND ru.nf_unitid = ug.nf_unitid
                             AND ug.nf_groupid = g.ip_groupid
                             AND ui.n_internal = 0
                             AND ui.nf_statusid = 1
                             AND u.nf_statusid = 1
                             AND ug.nf_groupid = 10
                             AND r.ip_roleid = 399
                    ORDER BY u.c_name;
                    • 7. Re: difference between two columns on a different table
                      indra budiantho
                      select (
                      select Benyttet from(
                      SELECT DISTINCT u.c_name "Forhandler"
                      , (SELECT n_grantedaid
                      FROM marketingaidgrant
                      WHERE nfp_periodid = 169
                      AND nfp_unitid = u.ip_unitid) "Tildelt"
                      , (SELECT SUM(n_aid)
                      FROM marketingaidapplication
                      WHERE nf_periodid = 169
                      AND nf_unitid = u.ip_unitid
                      AND to_char(D_DATECREATED, 'yyyymm') in ('201202') ) "Benyttet"
                      , (SELECT n_grantedaid
                      FROM marketingaidgrant
                      WHERE nfp_periodid = 169
                      AND nfp_unitid = u.ip_unitid)
                      )
                      -
                      (SELECT SUM(n_aid)
                      FROM marketingaidapplication
                      WHERE nf_periodid = 169
                      AND nf_unitid = u.ip_unitid
                      AND to_char(D_DATECREATED, 'yyyymm') in ('201202')) "Tilgjengelig"
                      FROM roleuser ru, unit u, role r, userinfo ui, unit_groups ug, groups g
                      WHERE ru.nf_unitid = u.ip_unitid
                      AND ru.nf_roleid = r.ip_roleid
                      AND ru.nf_userid = ui.ip_userid
                      AND ru.nf_unitid = ug.nf_unitid
                      AND ug.nf_groupid = g.ip_groupid
                      AND ui.n_internal = 0
                      AND ui.nf_statusid = 1
                      AND u.nf_statusid = 1
                      AND ug.nf_groupid = 10
                      AND r.ip_roleid = 399
                      ORDER BY u.c_name) 
                      )
                      from dual;
                      Edited by: Angry Oracle on May 16, 2012 2:57 AM
                      • 8. Re: difference between two columns on a different table
                        846290
                        Hi,

                        MINUS is a SET operator and not a arithmetic operator and hence it will not help. You can use the below query (Note I dont have access to a database and hence the same might give you some errors)
                        SELECT n_grantedaid -
                               (SELECT SUM(n_aid) 
                                  FROM marketingaidapplication
                                 WHERE nf_periodid = 169
                                   AND nf_unitid = u.ip_unitid
                                   AND to_char(D_DATECREATED, 'yyyymm') in ('201202'))
                          FROM marketingaidgrant
                         WHERE nfp_periodid = 169
                           AND nfp_unitid = u.ip_unitid
                        • 9. Re: difference between two columns on a different table
                          927867
                          thank you so much guys.. for helping.. :) its working now..

                          i need one more help on this query.. im not sure how to get the percent.

                          if you check on my query where i qouted it..

                          SELECT round(SUM(n_aid) / SUM(n_aid) over () * 100,2)
                          FROM marketingaidapplication
                          WHERE nf_periodid = 169
                          -AND nf_unitid = u.ip_unitid) "% Benyttet"

                          thank you sooo much
                          • 10. Re: difference between two columns on a different table
                            indra budiantho
                            select 50|| '%' "benettt"
                            from dual;
                            • 11. Re: difference between two columns on a different table
                              927867
                              okay thanks.. ill try this.. ill let you know if what will happen.. :)

                              again thanks for helping.. :)
                              • 12. Re: difference between two columns on a different table
                                Igor.M
                                select n_aid, 
                                        SUM(n_aid) over() sum_all, 
                                        SUM(n_aid) over (order by n_aid) sum_to_n_aid,
                                        round(SUM(n_aid) over (order by n_aid)/SUM(n_aid) over()*100,2) "%"
                                from
                                    (
                                    select level n_aid from dual connect by level <= 10
                                    );
                                
                                     N_AID    SUM_ALL SUM_TO_N_AID          %
                                ---------- ---------- ------------ ----------
                                         1         55            1       1,82
                                         2         55            3       5,45
                                         3         55            6      10,91
                                         4         55           10      18,18
                                         5         55           15      27,27
                                         6         55           21      38,18
                                         7         55           28      50,91
                                         8         55           36      65,45
                                         9         55           45      81,82
                                        10         55           55        100
                                
                                10 rows selected.