1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help on below query

Discussion in 'SQL PL/SQL' started by Samim, May 26, 2016.

  1. Samim

    Samim Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Kabul
    I have a requirement where i want to pick the result from following two tables in below format.

    t1:
    a b
    1 10
    1 20

    t2:
    a b
    1 50
    1 70

    I want the sum of b column for both t1 and t2 where a is having value of 1 to be displayed as below.

    t1.a sum(t1.b) sum(t2.b)
    1 30 120

    The following query can achieve this but i would like to know if we can do it in more sufficient way.

    select t1.a,
    sum(t1.b) , t2.b
    from t1 , (select t2.a, sum(t2.b) b from t2 group by t2.a ) t2
    where t1.a=t2.a
    group by t1.a , t2.b

    Thanks for the help
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    the choice of version of the decision can depend on a set of the reasons.

    perhaps, the decision would be other acceptable option :
    Code (SQL):

    WITH
    agg_t1 AS (SELECT a ,SUM(b) b FROM t1 GROUP BY a),
    agg_t2 AS (SELECT a ,SUM(b) b FROM t2 GROUP BY a)

    SELECT
      t1.a,t1.b,t2.b
    FROM agg_t1 t1,agg_t2 t2
    WHERE t1.a = t2.a;
     
    OR
    Code (SQL):

    SELECT
      t1.a,t1.b,t2.b
    FROM
    agg_t1 AS (SELECT a ,SUM(b) b FROM t1 GROUP BY a) t1 ,
    agg_t2 AS (SELECT a ,SUM(b) b FROM t2 GROUP BY a) t2
    WHERE t1.a = t2.a;
     
    OR
    only test... ))
    Code (SQL):
    SELECT
      t1.a,SUM(DISTINCT t1.b) t1_b,SUM(DISTINCT t2.b) t2_b
    FROM  t1,t2
    WHERE t1.a = t2.a
    GROUP BY t1.a
     
    Last edited: May 26, 2016
    Samim likes this.
  3. Samim

    Samim Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Kabul

    Dear krasnolobodtsev_si,

    Thanks for the help this really helped me a lot.

    Regards,