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!

How to get Column totals

Discussion in 'SQL PL/SQL' started by Tariq Bashir Malhi, Jan 29, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Out of following query is 37 rows, i want to include last row as column totals can you guide me....... For first two columns totals are not required

    SELECT c.sr_no, c.NAME, c.employer_target, b.employers_registered,

    c.employees_target, b.employees_registered, c.contribution_target,

    a.contribution, c.arrear_target, a.arrears, a.total_contr,

    b.pr02a_target, c.pr02a_entered

    FROM t_set1 c, t_set3 a, t_set2 b

    WHERE c.rgn = a.rgn4 AND c.rgn = b.rgn1

    ORDER BY 1;:hurray:hurray:hurray
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    This appears to be a simple issue of knowing how SQL*Plus functions:

    Code (SQL):
    break ON sr_no ON name
    compute SUM OF employer_target employers_registered employees_target employees_registered contribution_target
    contribution arrear_target arrears total_contr pr02a_target pr02a_entered
    ON name

    SELECT   c.sr_no, c.NAME, c.employer_target, b.employers_registered,
                c.employees_target, b.employees_registered, c.contribution_target,
                a.contribution, c.arrear_target, a.arrears, a.total_contr,
                b.pr02a_target, c.pr02a_entered
           FROM t_set1 c, t_set3 a, t_set2 b
          WHERE c.rgn = a.rgn4 AND c.rgn = b.rgn1
       ORDER BY 1;
    Should give you what you want.
     
  3. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    I need column totals through same select statement as i have to use this result set in php script.

    Pl. give other then compute solution..
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your requirement doesn't allow for such totals in the query statement:

    "Out of following query is 37 rows, i want to include last row as column totals."

    You want the totals, by column, at the end of this report; you can generate totals in the query but they will appear on every row returned by that query, not as a separate row at the end. You could write a PL/SQL block to generate such output; you could also write a sum query and union that to the original query however the output from the unioned query may not appear as the last row in your result set. I have no database available for testing such constructs so you'll need to experiment on your own.
     
  5. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    I have done it by this way...........

    Code (SQL):
    SELECT c.employer_target "cal1", b.employers_registered "cal2",

           c.employees_target "cal3", b.employees_registered "cal4",

           c.contribution_target "cal5", a.contribution "cal6",

           c.arrear_target "cal7", a.arrears "cal8", a.total_contr "cal9",

           b.pr02a_target "cal10", c.pr02a_entered "cal11"

      FROM core_business.t_set1 c, core_business.t_set3 a, core_business.t_set2 b

     WHERE c.rgn = a.rgn4 AND c.rgn = b.rgn1

    UNION

    SELECT SUM (cal1) "cal1", SUM (cal2) "cal2", SUM (cal3) "cal3",

           SUM (cal4) "cal4", SUM (cal5) "cal5", SUM (cal6) "cal6",

           SUM (cal7) "cal7", SUM (cal8) "cal8", SUM (cal9) "cal9",

           SUM (cal10) "cal10", SUM (cal11) "cal11"

      FROM (SELECT   c.employer_target cal1, b.employers_registered cal2,

                     c.employees_target cal3, b.employees_registered cal4,

                     c.contribution_target cal5, a.contribution cal6,

                     c.arrear_target cal7, a.arrears cal8, a.total_contr cal9,

                     b.pr02a_target cal10, c.pr02a_entered cal11

                FROM core_business.t_set1 c,

                     core_business.t_set3 a,

                     core_business.t_set2 b

               WHERE c.rgn = a.rgn4 AND c.rgn = b.rgn1

            ORDER BY 1)
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Using UNION, rather than UNION ALL, may produce undesired results as it may not put the totals as the last line of the output. Be aware of this behaviour.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    Some small thing to do.

    You can combine inner and outer query in the Select statement After Union clause. ie you have to use only the sum(..) select query After UNION clause.

    For getting the SUM in the last line , you can use an an extra field for order by clause. See One example.

    Code (SQL):

    SELECT  SAL FROM  (
    SELECT ROWNUM R, SAL FROM EMP  UNION  
    SELECT 2 , SUM(SAL)SAL  FROM EMP ORDER BY 1);